咨询热线 ：
028-86922220
×
01
关于我们

公司简介

发展历程

02
服务项目

高端网站建设

微信开发

APP开发

网络营销服务

电商网站定制

生物医药网站建设

外贸网站建设

教育培训网站建设

03
小程序开发

小程序开发

功能应用

客户案例

04
案例展示

网站建设案例

小程序案例

电商平台案例

APP案例

系统平台案例

05
建站动态

网站建设

网站设计

常见问题

小程序

06
联系我们

公司地址

人才招聘

地址：成都市太升南路288号锦天国际A幢1002号

电话：028-86922220

028-86922220

重庆分公司，新征程启航

为企业提供网站建设、域名注册、服务器等服务

【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 --
分享标题：【RMAN】使用RMAN备份将数据库不完全恢复到指定时间点
文章分享：http://cqcxhl.com/article/gohchp.html

其他资讯

分享至：
关于我们
公司简介 发展历程
服务项目
高端网站建设 小程序开发 APP开发 网络营销
建站知识
网站建设 网站设计 网站制作 小程序
联系我们
公司地址 人才招聘
成都 重庆
电话：028-86922220
地址：成都市太升南路288号锦天国际A幢1002号
电话：028-86922220
地址：重庆市南岸区弹子石腾龙大道58号2栋21-6
版权所有：成都创新互联科技有限公司重庆分公司 渝ICP备2021005571号-4
在线咨询
服务热线
服务热线：028-86922220
TOP