gdufo

 

在归档模式下的基于时间点的不完全恢复

在归档模式下的基于时间点的不完全恢复
sql>shutdown immediate

==
  对数据进行全备份  full backup database
==
sql> startup

==
  修改数据
  update test2 set.. 10:44:01
  drop table test1 10:45:05 ==>错误操作
==
sql>shutdown immediate

==
  备份事故现场
注意:
  在实际生产环境中,在做恢复的之前,一定要全备份目前的数据库,即:保存现场
  因为在恢复的过程不一定是一次可以恢复到我想得到状态。
==
  restore datafile
  现将之前全备份的所有数据文件(*.dbf)Copy回,不要恢复control file, redo logs等
==
sql>startup mount
sql>recover database until time '2009-11-27:10:45:01'
   Media recovery complete.
sql>alter database open resetlogs;
    Database altered
sql>conn test/test
sql>select table_name from user_tables;
    TABLE_NAME
    ----------
     TEST           
     TEST1          
     TEST2    
==
   这里发现之前drop 掉的table test1没有被删除了。

   接下来又要对整个数据进行一次全备份,因为以前的归档日志和数据都没有用了。
==     


在归档模式下的基于Cancel的不完全恢复,当前在线日志损坏

sql>shutdown immediate

==
  对数据进行全备份  full backup database
==
sql> startup

==
当前状态
==
sql> select table_name from user_tables

     TABLE_NAME
     ----------
     TEST

sql> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
         1          1          0  104857600          1 YES UNUSED
         2          1          0  104857600          1 YES UNUSED
         3          1          1  104857600          1 NO  CURRENT          26-N

sql> alter system switch logfile;
sql> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
         1          1          2  104857600          1 NO  CURRENT          27-N
         2          1          0  104857600          1 YES UNUSED
         3          1          1  104857600          1 YES ACTIVE           26-N

sql> create table test2 as select * from test;
sql> select table_name from user_tables
TABLE_NAME
----------
TEST
TEST2

sql> alter system switch logfile;
sql> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
         1          1          2  104857600          1 YES ACTIVE           27-N
         2          1          3  104857600          1 NO  CURRENT          27-N
         3          1          1  104857600          1 YES INACTIVE         26-N

sql> create table test3 as select * from test;
sql> select table_name from user_tables;
TABLE_NAME
-----------
TEST
TEST2
TEST3

sql> alter system switch logfile;
sql> select * from v$log;  ==>日志正好走完一个循环
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
         1          1          2  104857600          1 YES INACTIVE         27-N
         2          1          3  104857600          1 YES ACTIVE           27-N
         3          1          4  104857600          1 NO  CURRENT          27-N

sql> create table test4 as select * from test;
sql> select table_name from user_tables;

TABLE_NAME
-----------
TEST
TEST2
TEST3
TEST4

==
  修改数据

==
sql>shutdown immediate
==
  备份事故现场
注意:
  在实际生产环境中,在做恢复的之前,一定要全备份目前的数据库,即:保存现场
  因为在恢复的过程不一定是一次可以恢复到我想得到状态。
==
  restore datafile
  现将之前全备份的所有数据文件(*.dbf)Copy回,不要恢复control file, redo logs等
  并模拟当前日志文件丢失,可以直接删除
==
sql>startup mount
sql>recover database until cancel
ORA-00279: change 220847 generated at 11/27/2009 08:28:25
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_1.ARC
ORA-00280: change 220847 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
输入:auto

ORA-00279: change 221514 generated at 11/27/2009 13:29:04 needed for thread 1
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_2.ARC
ORA-00280: change 221514 for thread 1 is in sequence #2
ORA-00278: log file 'G:\ORACLE\ARC\ORCL_1_1.ARC' no longer needed for this
recovery


ORA-00279: change 222100 generated at 11/27/2009 13:32:35 needed for thread 1
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_3.ARC
ORA-00280: change 222100 for thread 1 is in sequence #3
ORA-00278: log file 'G:\ORACLE\ARC\ORCL_1_2.ARC' no longer needed for this
recovery


ORA-00279: change 222367 generated at 11/27/2009 13:34:18 needed for thread 1
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_4.ARC
ORA-00280: change 222367 for thread 1 is in sequence #4
ORA-00278: log file 'G:\ORACLE\ARC\ORCL_1_3.ARC' no longer needed for this
recovery


ORA-00308: cannot open archived log 'G:\ORACLE\ARC\ORCL_1_4.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 靠靠靠靠靠

SQL> recover database until cancel #再次输入
ORA-00279: change 222367 generated at 11/27/2009 13:34:18 needed for thread 1
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_4.ARC
ORA-00280: change 222367 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel #选Cancel因为此处ORCL_1_4.ARC没有归档
Media recovery cancelled.
SQL> alter database open resetlogs; #一定要 open resetlogs 重新生成log文件

Database altered.


sql>conn test/test
sql>select table_name from user_tables;
    TABLE_NAME
    ----------
     TEST           
     TEST1          
     TEST2   
     TEST3
==
   这里发现在恢复前当前日志的操作没有办法救回。TEST4没有办法找回了。

   接下来又要对整个数据进行一次全备份,因为以前的归档日志和数据都没有用了。
==     

posted on 2009-12-01 14:53 gdufo 阅读(499) 评论(0)  编辑  收藏 所属分类: Database (oracle, sqlser,MYSQL)

导航

统计

常用链接

留言簿(6)

随笔分类

随笔档案

文章分类

文章档案

收藏夹

Hibernate

友情链接

搜索

最新评论

阅读排行榜

评论排行榜