【RMAN】使用RMAN备份将数据库不完全恢复到指定时间点
发表于:2024-10-16 作者:千家信息网编辑
千家信息网最后更新 2024年10月16日,RMAN作为Oracle强大的备份恢复工具,可以协助我们恢复数据库到指定时间点,这便是Oracle不完全恢复的一种体现,通过这种方法可以找回我们曾经丢失的数据。这里以找回误TRUNCATE表数据为例给
千家信息网最后更新 2024年10月16日【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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 --
数据
数据库
备份
时间
文件
故障
强大
工具
方法
日志
脚本
控制
重要
成功
不可或缺
信息
功能
只是
小结
帮手
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
网络安全阀门有用吗
米内网数据库 试用账号
怎么把安卓平板改成服务器
东安区闵旦软件开发店
网络安全防护书籍
郑州菲特网络技术有限公司
想编程软件学什么软件开发
基层军队网络安全状态
成都梦谷时代网络技术有限公司
福州直播app软件开发
ios 开启ftp服务器
上海做软件开发的工资水平
贵州讯付宝网络技术
网络安全基础试讲
杭州市乐刻网络技术有限公司
嘉定区品牌软件开发业务流程
数据库数据导入网页并可操作
中专计算机网络技术知识点
软件开发 技术提升培训计划
杭州 软件开发
网络安全防护书籍
2015山东软件开发公司
高防100g服务器多少圈
网站服务器安全配置
免费的多维数据库
网络安全保卫是干嘛的
格力嵌入式软件开发
软件公司用什么软件开发app
数据库的最新技术总结
国外车辆网络安全的研究