oracle日志备份恢复

原创 2015年07月09日 15:55:20

1. 0级全备

备份前数据

SQL> select * from duanbb.t1;

        ID NAME
---------- --------------------------------------------------
         1 111

rman全备

RMAN> backup incremental level 0 database;

Starting backup at 09-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle11/app/oracle11/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle11/app/oracle11/oradata/orcl/system01.dbf
input datafile file number=00003 name=/home/oracle11/app/oracle11/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/home/oracle11/app/oracle11/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JUL-15
channel ORA_DISK_1: finished piece 1 at 09-JUL-15
piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp tag=TAG20150709T022947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 09-JUL-15
channel ORA_DISK_1: finished piece 1 at 09-JUL-15
piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_ncsn0_TAG20150709T022947_bsw58yp1_.bkp tag=TAG20150709T022947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JUL-15

2. 插入一条数据,进行日志备份

SQL> insert into duanbb.t1 values(2, '222');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

SQL> select * from duanbb.t1;

        ID NAME
---------- --------------------------------------------------
         1 111
         2 222

RMAN> backup archivelog all delete input;

Starting backup at 09-JUL-15
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=3 RECID=473 STAMP=884557194
input archived log thread=1 sequence=4 RECID=474 STAMP=884559659
input archived log thread=1 sequence=5 RECID=475 STAMP=884568650
input archived log thread=1 sequence=6 RECID=476 STAMP=884571491
input archived log thread=1 sequence=7 RECID=477 STAMP=884572546
input archived log thread=1 sequence=8 RECID=478 STAMP=884572648
channel ORA_DISK_1: starting piece 1 at 09-JUL-15
channel ORA_DISK_1: finished piece 1 at 09-JUL-15
piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp tag=TAG20150709T023728 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_08/o1_mf_1_3_bsvpl9xj_.arc RECID=473 STAMP=884557194
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_08/o1_mf_1_4_bsvrzcfm_.arc RECID=474 STAMP=884559659
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_5_bsw1rb2n_.arc RECID=475 STAMP=884568650
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_6_bsw4k3pw_.arc RECID=476 STAMP=884571491
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_7_bsw5l2jy_.arc RECID=477 STAMP=884572546
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_8_bsw5o8fs_.arc RECID=478 STAMP=884572648
Finished backup at 09-JUL-15

3. 再次插入一条数据,备份日志

SQL> insert into duanbb.t1 values(3, '333');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

RMAN> backup archivelog all delete input;

Starting backup at 09-JUL-15
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=9 RECID=479 STAMP=884572860
input archived log thread=1 sequence=10 RECID=480 STAMP=884573073
channel ORA_DISK_1: starting piece 1 at 09-JUL-15
channel ORA_DISK_1: finished piece 1 at 09-JUL-15
piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T024433_bsw62kjx_.bkp tag=TAG20150709T024433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_9_bsw5vwjy_.arc RECID=479 STAMP=884572860
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_10_bsw62kbg_.arc RECID=480 STAMP=884573073
Finished backup at 09-JUL-15


三次备份生成的文件:

第一次:全备
  o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp
  o1_mf_ncsn0_TAG20150709T022947_bsw58yp1_.bkp

第二次:日志  
  o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp

第三次: 日志    
  o1_mf_annnn_TAG20150709T024433_bsw62kjx_.bkp


4. 完全恢复(自动恢复到最新状态)

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1221992448 bytes

Fixed Size                     1336176 bytes
Variable Size                939527312 bytes
Database Buffers             268435456 bytes
Redo Buffers                  12693504 bytes
RMAN> run {
2>   allocate channel c1 type disk;
3>   restore database;
4>   recover database;
5>   sql 'alter database open';
6> }

allocated channel: c1
channel c1: SID=63 device type=DISK

Starting restore at 09-JUL-15

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /home/oracle11/app/oracle11/oradata/orcl/system01.dbf
channel c1: restoring datafile 00002 to /home/oracle11/app/oracle11/oradata/orcl/sysaux01.dbf
channel c1: restoring datafile 00003 to /home/oracle11/app/oracle11/oradata/orcl/undotbs01.dbf
channel c1: restoring datafile 00004 to /home/oracle11/app/oracle11/oradata/orcl/users01.dbf
channel c1: reading from backup piece /home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp
channel c1: piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp tag=TAG20150709T022947
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:36
Finished restore at 09-JUL-15

Starting recover at 09-JUL-15

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=7
channel c1: restoring archived log
archived log thread=1 sequence=8
channel c1: reading from backup piece /home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp
channel c1: piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp tag=TAG20150709T023728
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_7_bsw6gt1l_.arc thread=1 sequence=7
channel default: deleting archived log(s)
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_7_bsw6gt1l_.arc RECID=482 STAMP=884573466
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_8_bsw6gt1s_.arc thread=1 sequence=8
channel default: deleting archived log(s)
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_8_bsw6gt1s_.arc RECID=481 STAMP=884573466
media recovery complete, elapsed time: 00:00:01
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=9
channel c1: restoring archived log
archived log thread=1 sequence=10
channel c1: reading from backup piece /home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T024433_bsw62kjx_.bkp
channel c1: piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T024433_bsw62kjx_.bkp tag=TAG20150709T024433
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
channel default: deleting archived log(s)
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_9_bsw6gwgm_.arc RECID=483 STAMP=884573468
channel default: deleting archived log(s)
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_10_bsw6gwgq_.arc RECID=484 STAMP=884573468
Finished recover at 09-JUL-15

sql statement: alter database open
released channel: c1
还原后的数据

SQL> select * from duanbb.t1;

        ID NAME
---------- --------------------------------------------------
         1 111
         2 222
         3 333

5. 按时间点恢复

比如不想恢复“3 333”这条数据,那么只需要恢复到备份2的时间点即可

通过备份出的文件名“o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp”提取时间戳"2015-07-09 02:37:28"

RMAN> run {
2>   allocate channel c1 type disk;
3>   SET UNTIL TIME "to_date('2015-07-09 02:37:28','yyyy-mm-dd hh24:mi:ss')";
4>   restore database;
5>   recover database;
6>   sql 'alter database open resetlogs';
7> }

allocated channel: c1
channel c1: SID=63 device type=DISK

executing command: SET until clause

Starting restore at 09-JUL-15

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /home/oracle11/app/oracle11/oradata/orcl/system01.dbf
channel c1: restoring datafile 00002 to /home/oracle11/app/oracle11/oradata/orcl/sysaux01.dbf
channel c1: restoring datafile 00003 to /home/oracle11/app/oracle11/oradata/orcl/undotbs01.dbf
channel c1: restoring datafile 00004 to /home/oracle11/app/oracle11/oradata/orcl/users01.dbf
channel c1: reading from backup piece /home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp
channel c1: piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp tag=TAG20150709T022947
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:45
Finished restore at 09-JUL-15

Starting recover at 09-JUL-15

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=7
channel c1: restoring archived log
archived log thread=1 sequence=8
channel c1: reading from backup piece /home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp
channel c1: piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp tag=TAG20150709T023728
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_7_bswfr68o_.arc thread=1 sequence=7
channel default: deleting archived log(s)
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_7_bswfr68o_.arc RECID=492 STAMP=884580934
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_8_bswfr69b_.arc thread=1 sequence=8
channel default: deleting archived log(s)
archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_8_bswfr69b_.arc RECID=491 STAMP=884580934
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-JUL-15

sql statement: alter database open resetlogs
released channel: c1
恢复后的数据

SQL> select * from duanbb.t1;

        ID NAME
---------- --------------------------------------------------
         1 111
         2 222


总结:

1. 0级全备

backup incremental level 0 database;

2. 日志备份

backup archivelog all delete input;

3. 完全恢复

run {
  allocate channel c1 type disk;
  restore database;
  recover database;
  sql 'alter database open';
}

4. 不完全恢复(按时间点)

run {
  allocate channel c1 type disk;
  SET UNTIL TIME "to_date('2015-07-09 02:37:28','yyyy-mm-dd hh24:mi:ss')";
  restore database;
  recover database;
  sql 'alter database open resetlogs';
}



Oracle 11G 基于日志的备份与恢复(热备份)

(1)将数据库设置为归档日志模式; (2)关闭数据库(SHUTDOWN IMMEDIATE); (3)冷备份数据库,将所有数据文件、控制文件、联机重做日志文件拷贝到另一目录保存; (4)重新启动数据库...
  • u011850815
  • u011850815
  • 2014年05月02日 00:28
  • 2309

MySQL备份恢复与日志

MySQL 数据库的备份与恢复 1.1 备份数据的意义 第一是保护公司的数据,第二是网站7*24小时提供服务 1.2 备份单个数据库参数使用 MySQL数据库自带了一个很好用的备份命令,就是m...
  • linuxlsq
  • linuxlsq
  • 2016年09月21日 11:52
  • 1138

oracle 12c数据库备份与恢复教程

深入oracle 12c数据库备份与恢复(优化RMAN性能、Oracle flashback技术) 课程讲师:小流老师  课程分类:Oracle 适合人群:高级 课时数量:15课时 用到技术...
  • atipa
  • atipa
  • 2014年09月10日 10:21
  • 1357

Oracle 11G Rman备份ASM数据恢复到本地磁盘

在日常工作中,我们经常会遇到需要将使用ASM存储的数据迁移到本地磁盘中,迁移之后的数据库可用于测试等用途。可以选择的工具很多,exp/imp、 expdp/impdp、rman,前两种方法因为比较适合...
  • hanzheng260561728
  • hanzheng260561728
  • 2016年04月24日 21:41
  • 1325

利用日志备份恢复时,提示 该 LSN 太晚,无法应用到数据库

/*      服务器:   消息   4305,级别   16,状态   1,行   2      此备份集中的日志开始于   LSN   641000000005900001,该   ...
  • YABIGNSHI
  • YABIGNSHI
  • 2015年12月28日 04:53
  • 1283

【SQL Server备份恢复】维护计划实现备份:每周数据库完整备份、每天差异备份、每小时日志备份

在数据库管理中,数据库备份是非常重要的。 通过维护计划向导,可以很方便的完成数据库备份。 下面的例子说明了如何实现数据库的备份,具体的备份策略是:每周日一次完整备份、每天差异备份(除周日外)、每小时...
  • yupeigu
  • yupeigu
  • 2013年09月03日 21:19
  • 13097

SQL Server2014备份与备份恢复之完全备份(差异备份/日志备份)

本手册以SQL Server 2014完全备份为实例,差异备份和日志备份方法只需要在第二步的第5点更改即可。 说明:本手册提供的所有备份操作,均使用测试数据库操作,数据内容不具有参考性,仅作为备份操...
  • u010300947
  • u010300947
  • 2014年07月16日 14:58
  • 3933

oracle 日志文件、参数文件、控制文件备份恢复

在此之前搞清楚每个文件的作用,一定要掌握oracle的基本概念。 一、         基本文件的备份恢复 1、logfile多元化和备份恢复 日志查询 select * from v$log...
  • yujin2010good
  • yujin2010good
  • 2012年04月04日 21:39
  • 5111

oracle备份恢复参考日志——实例恢复

http://blog.csdn.net/robinson_0612/article/details/5768233 一、Oracle实例失败     Oracle实例失败多为实例非一致性关闭...
  • changyanmanman
  • changyanmanman
  • 2012年08月17日 20:45
  • 1329

oracle日志文件、参数文件和控制文件的备份恢复

pfile 和 spfile 的区别  参数文件备份恢复 用pfile文件备份二进制的启动参数文件 create pfile from spfile; create pfile = '...
  • nosodeep
  • nosodeep
  • 2013年02月27日 23:22
  • 260
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle日志备份恢复
举报原因:
原因补充:

(最多只允许输入30个字)