【RMAN】使用RMAN備份將資料庫不完全恢復到指定時間點
RMAN作為Oracle強大的備份恢復工具，可以協助我們恢復資料庫到指定時間點，這便是Oracle不完全恢復的一種體現，透過這種方法可以找回我們曾經丟失的資料。這裡以找回誤TRUNCATE表資料為例給大家演示一下RMAN的不完全恢復功能。
1.調整資料庫為歸檔模式
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:10:38 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 73
Current log sequence 77
sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora10g> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 318767252 bytes
Database Buffers 209715200 bytes
Redo Buffers 7168000 bytes
Database mounted.
sys@ora10g> alter database archivelog;
Database altered.
sys@ora10g> alter database open;
Database altered.
2.使用RMAN對資料庫進行備份
1）備份資料庫
ora10g@secdb /home/oracle$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 19 22:16:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G (DBID=4067278754)
RMAN> backup database;
Starting backup at 20111019 22:16:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=214 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00010 name=/oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
input datafile fno=00003 name=/oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
input datafile fno=00001 name=/oracle/ora10gR2/oradata/ora10g/system01.dbf
input datafile fno=00002 name=/oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
input datafile fno=00005 name=/home/oracle/tbs_sec_d_01.dbf
input datafile fno=00004 name=/oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
input datafile fno=00008 name=/oracle/ora10gR2/oradata/ora10g/tbs01.dbf
input datafile fno=00009 name=/oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
input datafile fno=00016 name=/u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
input datafile fno=00017 name=/u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
input datafile fno=00007 name=/oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
input datafile fno=00006 name=/oracle/ora10gR2/oradata/ora10g/users.dbf
channel ORA_DISK_1: starting piece 1 at 20111019 22:16:36
channel ORA_DISK_1: finished piece 1 at 20111019 22:17:41
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00011 name=/u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
input datafile fno=00012 name=/u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
input datafile fno=00013 name=/u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
input datafile fno=00014 name=/u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
input datafile fno=00015 name=/u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf
channel ORA_DISK_1: starting piece 1 at 20111019 22:17:42
channel ORA_DISK_1: finished piece 1 at 20111019 22:17:45
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20111019 22:17:45
Starting Control File and SPFILE Autobackup at 20111019 22:17:45
piece handle=/db_backup/rman_backup/c-4067278754-20111019-00 comment=NONE
Finished Control File and SPFILE Autobackup at 20111019 22:17:48
2）檢視備份資訊
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
48 Full 1.10G DISK 00:00:57 20111019 22:17:33
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221636
Piece Name: /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp
List of Datafiles in backup set 48
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/system01.dbf
2 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
3 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
4 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
5 Full 6494715 20111019 22:16:36 /home/oracle/tbs_sec_d_01.dbf
6 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/users.dbf
7 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
8 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs01.dbf
9 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
10 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
16 Full 6494715 20111019 22:16:36 /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
17 Full 6494715 20111019 22:16:36 /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
49 Full 1.02M DISK 00:00:01 20111019 22:17:43
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221636
Piece Name: /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp
List of Datafiles in backup set 49
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
11 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
12 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
13 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
14 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
15 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
50 Full 6.83M DISK 00:00:00 20111019 22:17:45
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221745
Piece Name: /db_backup/rman_backup/c-4067278754-20111019-00
Control File Included: Ckp SCN: 6494743 Ckp time: 20111019 22:17:45
SPFILE Included: Modification time: 20111019 22:11:53
3.模擬資料庫故障——表的誤TRUNCATE
1）連線到資料庫的sec使用者
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:20:57 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> conn sec/oracle_1
Connected.
2）檢視T表中的資料行數
sec@ora10g> select count(*) from t;
COUNT(*)
----------
1000
此時T表中包含1000條資料。
3）檢視當前時間，以便後續使用RMAN進行恢復
sec@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
sec@ora10g> select sysdate from dual;
SYSDATE
-------------------
2011-10-19 22:21:38
4）這裡模擬對T表的誤刪除（DDL型別的TRUNCATE方法）
sec@ora10g> truncate table t;
Table truncated.
sec@ora10g> select sysdate from dual;
SYSDATE
-------------------
2011-10-19 22:22:05
sec@ora10g> select count(*) from t;
COUNT(*)
----------
0
4.使用RMAN恢復到故障發生之前的時間點
我們這裡恢復的時間點的目標是T表被刪除之前的2011-10-19 22:21:38時刻。
【重要提醒】在使用RMAN完成基於時間點的不完全恢復之前，最好對現場做一個備份，我們這裡只需要備份資料庫的控制檔案和日誌檔案即可。當恢復結束後不滿足我們要求時，可以恢復控制檔案和日誌檔案後重新進行恢復。
1）將資料庫啟動到mount狀態
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora10g> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 318767252 bytes
Database Buffers 209715200 bytes
Redo Buffers 7168000 bytes
Database mounted.
2）使用RMAN指令碼恢復資料庫到指定時間點
（1）恢復指令碼如下
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 = '2011-10-19 22:21:38';
restore database;
recover database;
alter database open resetlogs;}
（2）恢復過程記錄
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
5> set until time = '2011-10-19 22:21:38';
6> restore database;
7> recover database;
8> alter database open resetlogs;}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=211 devtype=DISK
allocated channel: c2
channel c2: sid=210 devtype=DISK
sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
executing command: SET until clause
Starting restore at 20111019 22:31:04
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/ora10gR2/oradata/ora10g/system01.dbf
restoring datafile 00002 to /oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
restoring datafile 00003 to /oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
restoring datafile 00004 to /oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
restoring datafile 00005 to /home/oracle/tbs_sec_d_01.dbf
restoring datafile 00006 to /oracle/ora10gR2/oradata/ora10g/users.dbf
restoring datafile 00007 to /oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
restoring datafile 00008 to /oracle/ora10gR2/oradata/ora10g/tbs01.dbf
restoring datafile 00009 to /oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
restoring datafile 00010 to /oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
restoring datafile 00016 to /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
restoring datafile 00017 to /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
channel c1: reading from backup piece /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
restoring datafile 00012 to /u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
restoring datafile 00013 to /u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
restoring datafile 00014 to /u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
restoring datafile 00015 to /u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf
channel c2: reading from backup piece /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp
channel c2: restored backup piece 1
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636
channel c2: restore complete, elapsed time: 00:00:08
channel c1: restored backup piece 1
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636
channel c1: restore complete, elapsed time: 00:00:53
Finished restore at 20111019 22:31:58
Starting recover at 20111019 22:31:58
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 20111019 22:32:00
database opened
released channel: c1
released channel: c2
5.驗證恢復成果
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:35:37 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g>
sys@ora10g>
sys@ora10g> conn sec/oracle_1
Connected.
sec@ora10g> select count(*) from t;
COUNT(*)
----------
1000
至此，曾經因TRUNCATE導致T表資料丟失的故障已被成功恢復。
6.小結
Oracle的RMAN工具非常強大，這裡只是給出了不完全恢復的一個常見用法。在日常定製Oracle資料庫備份恢復策略的時候RMAN是我們不可或缺的好幫手。
Good luck.
secooler
11.10.19
-- The End --
1.調整資料庫為歸檔模式
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:10:38 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 73
Current log sequence 77
sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora10g> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 318767252 bytes
Database Buffers 209715200 bytes
Redo Buffers 7168000 bytes
Database mounted.
sys@ora10g> alter database archivelog;
Database altered.
sys@ora10g> alter database open;
Database altered.
2.使用RMAN對資料庫進行備份
1）備份資料庫
ora10g@secdb /home/oracle$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 19 22:16:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G (DBID=4067278754)
RMAN> backup database;
Starting backup at 20111019 22:16:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=214 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00010 name=/oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
input datafile fno=00003 name=/oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
input datafile fno=00001 name=/oracle/ora10gR2/oradata/ora10g/system01.dbf
input datafile fno=00002 name=/oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
input datafile fno=00005 name=/home/oracle/tbs_sec_d_01.dbf
input datafile fno=00004 name=/oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
input datafile fno=00008 name=/oracle/ora10gR2/oradata/ora10g/tbs01.dbf
input datafile fno=00009 name=/oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
input datafile fno=00016 name=/u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
input datafile fno=00017 name=/u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
input datafile fno=00007 name=/oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
input datafile fno=00006 name=/oracle/ora10gR2/oradata/ora10g/users.dbf
channel ORA_DISK_1: starting piece 1 at 20111019 22:16:36
channel ORA_DISK_1: finished piece 1 at 20111019 22:17:41
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00011 name=/u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
input datafile fno=00012 name=/u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
input datafile fno=00013 name=/u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
input datafile fno=00014 name=/u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
input datafile fno=00015 name=/u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf
channel ORA_DISK_1: starting piece 1 at 20111019 22:17:42
channel ORA_DISK_1: finished piece 1 at 20111019 22:17:45
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20111019 22:17:45
Starting Control File and SPFILE Autobackup at 20111019 22:17:45
piece handle=/db_backup/rman_backup/c-4067278754-20111019-00 comment=NONE
Finished Control File and SPFILE Autobackup at 20111019 22:17:48
2）檢視備份資訊
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
48 Full 1.10G DISK 00:00:57 20111019 22:17:33
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221636
Piece Name: /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp
List of Datafiles in backup set 48
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/system01.dbf
2 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
3 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
4 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
5 Full 6494715 20111019 22:16:36 /home/oracle/tbs_sec_d_01.dbf
6 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/users.dbf
7 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
8 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs01.dbf
9 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
10 Full 6494715 20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
16 Full 6494715 20111019 22:16:36 /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
17 Full 6494715 20111019 22:16:36 /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
49 Full 1.02M DISK 00:00:01 20111019 22:17:43
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221636
Piece Name: /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp
List of Datafiles in backup set 49
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
11 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
12 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
13 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
14 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
15 Full 6494738 20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
50 Full 6.83M DISK 00:00:00 20111019 22:17:45
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20111019T221745
Piece Name: /db_backup/rman_backup/c-4067278754-20111019-00
Control File Included: Ckp SCN: 6494743 Ckp time: 20111019 22:17:45
SPFILE Included: Modification time: 20111019 22:11:53
3.模擬資料庫故障——表的誤TRUNCATE
1）連線到資料庫的sec使用者
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:20:57 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g> conn sec/oracle_1
Connected.
2）檢視T表中的資料行數
sec@ora10g> select count(*) from t;
COUNT(*)
----------
1000
此時T表中包含1000條資料。
3）檢視當前時間，以便後續使用RMAN進行恢復
sec@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
sec@ora10g> select sysdate from dual;
SYSDATE
-------------------
2011-10-19 22:21:38
4）這裡模擬對T表的誤刪除（DDL型別的TRUNCATE方法）
sec@ora10g> truncate table t;
Table truncated.
sec@ora10g> select sysdate from dual;
SYSDATE
-------------------
2011-10-19 22:22:05
sec@ora10g> select count(*) from t;
COUNT(*)
----------
0
4.使用RMAN恢復到故障發生之前的時間點
我們這裡恢復的時間點的目標是T表被刪除之前的2011-10-19 22:21:38時刻。
【重要提醒】在使用RMAN完成基於時間點的不完全恢復之前，最好對現場做一個備份，我們這裡只需要備份資料庫的控制檔案和日誌檔案即可。當恢復結束後不滿足我們要求時，可以恢復控制檔案和日誌檔案後重新進行恢復。
1）將資料庫啟動到mount狀態
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora10g> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 318767252 bytes
Database Buffers 209715200 bytes
Redo Buffers 7168000 bytes
Database mounted.
2）使用RMAN指令碼恢復資料庫到指定時間點
（1）恢復指令碼如下
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 = '2011-10-19 22:21:38';
restore database;
recover database;
alter database open resetlogs;}
（2）恢復過程記錄
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
5> set until time = '2011-10-19 22:21:38';
6> restore database;
7> recover database;
8> alter database open resetlogs;}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=211 devtype=DISK
allocated channel: c2
channel c2: sid=210 devtype=DISK
sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
executing command: SET until clause
Starting restore at 20111019 22:31:04
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/ora10gR2/oradata/ora10g/system01.dbf
restoring datafile 00002 to /oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
restoring datafile 00003 to /oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
restoring datafile 00004 to /oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
restoring datafile 00005 to /home/oracle/tbs_sec_d_01.dbf
restoring datafile 00006 to /oracle/ora10gR2/oradata/ora10g/users.dbf
restoring datafile 00007 to /oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
restoring datafile 00008 to /oracle/ora10gR2/oradata/ora10g/tbs01.dbf
restoring datafile 00009 to /oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
restoring datafile 00010 to /oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
restoring datafile 00016 to /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
restoring datafile 00017 to /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
channel c1: reading from backup piece /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
restoring datafile 00012 to /u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
restoring datafile 00013 to /u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
restoring datafile 00014 to /u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
restoring datafile 00015 to /u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf
channel c2: reading from backup piece /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp
channel c2: restored backup piece 1
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636
channel c2: restore complete, elapsed time: 00:00:08
channel c1: restored backup piece 1
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636
channel c1: restore complete, elapsed time: 00:00:53
Finished restore at 20111019 22:31:58
Starting recover at 20111019 22:31:58
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 20111019 22:32:00
database opened
released channel: c1
released channel: c2
5.驗證恢復成果
ora10g@secdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:35:37 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ora10g>
sys@ora10g>
sys@ora10g> conn sec/oracle_1
Connected.
sec@ora10g> select count(*) from t;
COUNT(*)
----------
1000
至此，曾經因TRUNCATE導致T表資料丟失的故障已被成功恢復。
6.小結
Oracle的RMAN工具非常強大，這裡只是給出了不完全恢復的一個常見用法。在日常定製Oracle資料庫備份恢復策略的時候RMAN是我們不可或缺的好幫手。
Good luck.
secooler
11.10.19
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-2138587/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [RMAN]使用RMAN備份將資料庫不完全恢復到指定時間點資料庫
- rman恢復到指定時間點
- RMAN一次RMAN將資料庫不完全恢復資料庫
- 恢復到特定點（時間點、scn、日誌序列號），rman不完全恢復
- rman資料庫全庫備份與恢復資料庫
- RMAN備份恢復——備份到帶庫的效能
- RAC資料庫的RMAN備份異機恢復到單節點資料庫資料庫
- RMAN基於時間點恢復Oracle資料庫Oracle資料庫
- rman備份恢復-rman恢復資料檔案測試
- rman給歸檔指定備份位置；庫先備份盤同時到帶庫；更改控制檔案自備路徑；時間點的恢復
- RMAN備份恢復典型案例——資料庫卡頓資料庫
- 非RMAN熱備份資料庫和恢復資料庫
- Oracle資料庫備份與恢復之RMANOracle資料庫
- RMAN備份恢復——備份到帶庫的效能（二）
- 【備份恢復】RMAN catalog 恢復目錄資料庫資料庫
- 資料庫恢復到備份之前測試_RMAN-06556資料庫
- Oracle Rman 資料庫的不完全恢復Oracle資料庫
- rman備份恢復-rman入門
- RMAN備份恢復整個庫
- RMAN備份恢復——RAC環境資料庫的備份(zt)資料庫
- RMAN備份恢復--RAC環境資料庫的備份（十）資料庫
- RMAN備份恢復——RAC環境資料庫的備份（一）資料庫
- 使用RMAN備份資料庫資料庫
- RMAN備份恢復原理
- 【RMAN】rman使用NORESTELOGS 方式恢復資料庫REST資料庫
- rman恢復資料庫--用備份的控制檔案資料庫
- Oracle資料庫備份與恢復之RMAN2Oracle資料庫
- DM7使用DMRMAN恢復資料庫到指定時間點/LSN資料庫
- ORACLE DG從庫 Rman備份恢復Oracle
- 使用RMAN還原資料庫到某個時間點資料庫
- 執行RMAN表空間及時點恢復——使用者管理備份和恢復手冊
- standby全庫rman備份檔案恢復到異機
- RMAN恢復資料庫資料庫
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- rman資料備份恢復學習筆記筆記
- 只存在RMAN備份片的資料庫恢復過程資料庫
- Oracle RMAN 不完全恢復（只有資料檔案備份，丟失歸檔日誌備份）Oracle
- RMAN備份恢復典型案例——RMAN備份&系統變慢