冷备份还原数据库能不能前滚恢复

原创 2004年09月24日 09:35:00

对于冷备份还原数据库能不能进行前滚恢复的问题,itpub论坛上相关链接在http://www.itpub.net/showthread.php?s=&threadid=220971
 
通过实验证明,冷备份还原数据库可以进行前滚恢复:
以下是实验过程:

SQL> connect  internal/oracle
Connected.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/arch/ORCL
Oldest online log sequence     747
Next log sequence to archive   749
Current log sequence           749

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1        748   51200000          1 YES INACTIVE
      5209950 21-SEP-04

         2          1        749   51200000          1 NO  CURRENT
      5222702 23-SEP-04

         3          1        747   51200000          1 YES INACTIVE
      5205433 21-SEP-04


SQL> select * from v$logfile;

    GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
         1
/opt/oracle/db02/oradata/ORCL/redo01.log

         2
/opt/oracle/db03/oradata/ORCL/redo02.log

         3
/opt/oracle/db04/oradata/ORCL/redo03.log


SQL> connect test/test
Connected.
SQL> select * from user_tables;

no rows selected

SQL> create table t(a varchar2(10));

Table created.

SQL> alter system switch logfile;     (切换日志,产生一个归档日志文件)

System altered.

SQL> insert into t values('1'); 

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;      (切换日志,产生一个归档日志文件)

System altered.
SQL> insert into t values('2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;       (切换日志,产生一个归档日志文件)

System altered.

SQL> insert into t values('3');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;     (切换日志,产生一个归档日志文件)    

System altered.   

SQL> host
$ cd /opt/oracle/arch/ORCL

$ls    (总共产生了四个归档日志)
arch_1_749.arc  arch_1_750.arc  arch_1_751.arc  arch_1_752.arc
SQL>exit
 
SQL> select * from t;

A
----------
1
2
3

SQL> insert  into t values('4');   (插入第四条记录4,但不切换日志,这条记录记录在redo03.log里)

1 row created.

SQL> commit;

Commit complete.


关闭数据库:
SQL> connect internal/oracle
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host
$cp /opt/oracle/db04/oradata/ORCL/redo03.log /opt/oracle/      (单独备份当前日志redo03.log到/opt/oracle目录)
$ rm /opt/oracle/db02/oradata/ORCL/*  
$
rm /opt/oracle/db03/oradata/ORCL/* 
$
rm /opt/oracle/db04/oradata/ORCL/*

此时cp冷备份时的所有数据文件(包括在线日志文件和控制文件)回去,此实验之前做的冷备份.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1134141116 bytes
Fixed Size                   102076 bytes
Variable Size             311750656 bytes
Database Buffers          819200000 bytes
Redo Buffers                3088384 bytes
Database mounted.

SQL> recover database using backup controlfile until cancel;  (注意这里一定要用using backup controlfile until cancel命令) 
ORA-00279: change 5224492 generated at 09/23/2004 11:00:18 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_749.arc
ORA-00280: change 5224492 for thread 1 is in sequence #749

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 5224671 generated at 09/24/2004 10:27:50 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_750.arc
ORA-00280: change 5224671 for thread 1 is in sequence #750
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_749.arc' no longer needed for
this recovery
ORA-00279: change 5224683 generated at 09/24/2004 10:31:23 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_753.arc
ORA-00280: change 5224683 for thread 1 is in sequence #753


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/redo03.log   (这里指定备份的那个/opt/oracle/redo03.log文件)
Log applied.
Media recovery complete.

SQL> conn test/test
Connected.
SQL> select * from t;


ORA-00279: change 5224675 generated at 09/24/2004 10:29:00 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_751.arc
ORA-00280: change 5224675 for thread 1 is in sequence #751
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_750.arc' no longer needed for
this recovery


ORA-00279: change 5224679 generated at 09/24/2004 10:30:44 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_752.arc
ORA-00280: change 5224679 for thread 1 is in sequence #752
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_751.arc' no longer needed for
this recovery


ORA-00279: change 5224683 generated at 09/24/2004 10:31:23 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_753.arc
ORA-00280: change 5224683 for thread 1 is in sequence #753
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_752.arc' no longer needed for
this recovery


ORA-00308: cannot open archived log '/opt/oracle/arch/ORCL/arch_1_753.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

SQL> alter database open resetlogs;  (此时用open resetlogs打开数据库)

Database altered.

SQL> connect test/test
Connected.
SQL> select * from t;

A
----------
1
2
3

结果发现数据库做了不完全恢复,只恢复了3条记录,第4条记录丢失了,因为冷备份后把redo03.log覆盖了,而冷备份时的redo03.log里无此记录,最后一条记录4记录实际上记录在冷备份后到出故障前的redo03.log里了,因此只能恢复3条记录。
如果应用了备份在/opt/oracle目录下的那个redo03.log,数据库就可以做完全恢复了。


在恢复的时候,最后一次恢复,指定备份的redo03.log:
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5224492 generated at 09/23/2004 11:00:18 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_749.arc
ORA-00280: change 5224492 for thread 1 is in sequence #749


SQL> recover database using backup controlfile until cancel;


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 5224671 generated at 09/24/2004 10:27:50 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_750.arc
ORA-00280: change 5224671 for thread 1 is in sequence #750
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_749.arc' no longer needed for
this recovery


ORA-00279: change 5224675 generated at 09/24/2004 10:29:00 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_751.arc
ORA-00280: change 5224675 for thread 1 is in sequence #751
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_750.arc' no longer needed for
this recovery


ORA-00279: change 5224679 generated at 09/24/2004 10:30:44 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_752.arc
ORA-00280: change 5224679 for thread 1 is in sequence #752
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_751.arc' no longer needed for
this recovery


ORA-00279: change 5224683 generated at 09/24/2004 10:31:23 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_753.arc
ORA-00280: change 5224683 for thread 1 is in sequence #753
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_752.arc' no longer needed for
this recovery


ORA-00308: cannot open archived log '/opt/oracle/arch/ORCL/arch_1_753.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

A
----------
1
2
3
4

发现完全恢复了数据。


总结:用冷备份时候的所有文件前滚恢复数据库,恢复的时候要指定 recover database using backup controlfile util cancel,直到所有的归档,应用完毕再手工apply 联机日志就可以了,否则会报控制文件老了不需要恢复的错误,注意最后一个日志要使用在线日志来恢复。



版权声明:本文为博主原创文章,未经博主允许不得转载。

DB2 数据库备份、恢复和前滚

  、 2003 年 3 月 01 日本文提供了执行数据库备份、恢复和前滚日志文件这些基本操作的循序渐进指南。以下示例将介绍这些实用程序句法上的变体,以便展示 DB2 产品的灵活性。简介本文提供了...
  • hanxin1987216
  • hanxin1987216
  • 2009年09月04日 09:44
  • 3807

自己动手讲述ORACLE异机还原冷备份数据库

转: http://www.cnblogs.com/hustsay23/archive/2011/11/05/2237748.html ORACLE数据库和SQL SERVER最大的区别是后者完...
  • Quincylk
  • Quincylk
  • 2016年10月20日 21:17
  • 481

Oracle 11G 冷备份与恢复

关闭数据库(SHUTDOWN IMMEDIATE); 将所有数据文件、控制文件、联机重做日志文件拷贝到另一目录保存; 重新启动数据库(STARTUP); 进行数据库更新操作,如删除若干记录,新增若干记...
  • u011850815
  • u011850815
  • 2014年05月02日 00:00
  • 2107

SDE For PostgreSQL物理迁移(冷备份,恢复)

环境: 源机器: IP:192.168.220.144 OS: Win7 PostgreSQL:9.4.10 for X64 ...
  • liufeng1980423
  • liufeng1980423
  • 2017年06月01日 14:58
  • 463

oracle11G 冷备异机还原数据库

1. 把冷备份的数据文件,日志文件和控制文件拷贝到目标文件夹 2、启动数据库 [root@ogg-node2 ~]# mkdir  /u02/app/oracle/oradata/gdb1/ -p [...
  • xiegh2014
  • xiegh2014
  • 2016年08月19日 21:46
  • 820

Oracle数据库的冷备份及冷备份异地恢复方法

Oracle数据库的冷备份及冷备份异地恢复方法 分类: Oracle2013-07-11 09:06 51人阅读 评论(0) 收藏 举报 如何对Oracle数据库进行冷备份呢?如果是冷备份异地...
  • haiross
  • haiross
  • 2013年10月17日 17:12
  • 2124

根据冷备份恢复数据库操作流程

建议: 1.目标数据库最好和源数据库安装路径一致,减少很多不必要的问题。 2.目标数据库和源数据库的版本和位数最好要一致,不然会要进行worldsize转码,会浪费很多的时间,目前不知道会留下什么后...
  • sunyx1130
  • sunyx1130
  • 2015年10月21日 09:29
  • 253

恢复OA系统数据库--ORACLE11G通过冷备份迁移数据-(很简单,很实用,特别是数据量不大时很好用)

恢复OA系统数据库ORACLE11G通过冷备份迁移数据-很简单很实用特别是数据量不大时很好用...
  • xuejiayue1105
  • xuejiayue1105
  • 2014年06月04日 15:33
  • 9799

冷备份还原数据库是不是不能前滚恢复?(zt)

http://www.itpub.net/showthread.php?s=&threadid=220971
  • lunar2000
  • lunar2000
  • 2004年07月22日 15:59
  • 845

前滚和回滚的区别

前滚:          未完全提交的事务,即该事务已经被执行commit命令了,只是现在该事务修改所对应的脏数据块中只有一部分被写到磁盘上的数据文件中,还有一部分已经被置为提交标记的脏块还在内存上,...
  • haiross
  • haiross
  • 2013年11月28日 12:00
  • 10676
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:冷备份还原数据库能不能前滚恢复
举报原因:
原因补充:

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