不完全恢复 - redo损坏 - 盐城小土包

不完全恢复 - redo损坏

恢复：
1. 完全恢复

恢复的起点
SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1191483
         2            1191483
         3            1191483
         4            1191483
         5            1191483
恢复的终点
SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1191483
         2            1191483
         3            1191483
         4            1191483
         5            1191483
我们需要的是：横向比较，各个文件之间进行比较

2. 不完全恢复

主动
SQL> select * from t;
ID                   SCN
-------------------- ----------------------------------------
1                    1168049
2                    1168427
3                    1168514
4                    1168523
5                    1168535

SQL> set time on
22:16:02 SQL> drop table t;
表已删除。
22:16:18 SQL>
-- 可以恢复到指定的时间（22:16:02）之前，可以主动要求
被动 - 日志文件损坏，无法恢复“终点”

添加新的日志成员 - 给日志文件做镜像

SQL> alter database add logfile member 'D:\app\Administrator\oradata\orcl\log\REDO01.LOG' to group 1;
数据库已更改。
SQL> alter database add logfile member 'D:\app\Administrator\oradata\orcl\log\REDO02.LOG' to group 2;
数据库已更改。
SQL> alter database add logfile member 'D:\app\Administrator\oradata\orcl\log\REDO03.LOG' to group 3;
数据库已更改。

--======刚开始都是INVALID状态，因为没有使用过======
SQL> select group#, sequence#, members from v$log;
    GROUP#  SEQUENCE#    MEMBERS
---------- ---------- ----------
         1         28          2
         2         29          2
         3         27          2

SQL> select status, member from v$logfile;
STATUS         MEMBER
-------------- --------------------------------------------------
               D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
               D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
               D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
INVALID        D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO01.LOG
INVALID        D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO02.LOG
INVALID        D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO03.LOG

--=====使用一次之后，就有状态了====
SQL> alter system switch logfile;
系统已更改。
SQL> select status, member from v$logfile;
STATUS         MEMBER
-------------- --------------------------------------------------
               D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
               D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
               D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
INVALID        D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO01.LOG
INVALID        D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO02.LOG
               D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO03.LOG

如果有日志文件有备份的镜像（member 坏了一个，但是还有别的），则不影响数据库的正常启动。
但是在跟踪日志文件中，还是会有一些“警告”提示。
NOTE一个member损害：

drop镜像，相当于没有做过镜像，修改控制文件...
SQL> alter database drop logfile member '*****';

增加/删除日志组

-- 没有指明group#，系统会自动搜索&使用最小编号
SQL> alter database add logfile  'E:\ORACLE\WPENG\WPENG\LOG\REDO04.LOG' size 10m;
Database altered.

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1        133   52428800          2 YES INACTIVE               2837020 15-OCT-12
         2          1        134   52428800          2 NO  CURRENT                2837025 15-OCT-12
         3          1        132   52428800          2 YES INACTIVE               2837012 15-OCT-12
         4          1          0   10485760          1 YES UNUSED                       0

-- 也可以自己指明所需要的group#
SQL> alter database add logfile group 6 'E:\ORACLE\WPENG\WPENG\LOG\REDO05.LOG' size 10m;
Database altered.

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1        133   52428800          2 YES INACTIVE               2837020 15-OCT-12
         2          1        134   52428800          2 NO  CURRENT                2837025 15-OCT-12
         3          1        132   52428800          2 YES INACTIVE               2837012 15-OCT-12
         4          1          0   10485760          1 YES UNUSED                       0
         6          1          0   10485760          1 YES UNUSED                       0

可以看到，刚添加的日志组，状态都是为UNUSED。

--drop不会删除物理日志文件
SQL> alter database drop logfile group 6;
Database altered.

SQL> alter database drop logfile group 4;
Database altered.

recover database until ***

recover database until scn
recover database until time
recover database until sequence#

？？？

恢复当下的 = 判别是否丢失数据

--关闭数据
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--=====删除当前日志文件组 group 3 所有的member file====

--启动数据库到mount状态
SQL> startup mount
ORACLE instance started.

Total System Global Area 1319546880 bytes
Fixed Size                  2114656 bytes
Variable Size             503319456 bytes
Database Buffers          805306368 bytes
Redo Buffers                8806400 bytes
Database mounted.

--启动数据库到open状态
--失败：找不到group 3 的 members
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\REDO03.LOG'
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\LOG\REDO03.LOG'

--尝试删除 group 3
--失败：group 3是当前日志组
SQL> alter database drop logfile group 3 ;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance wpeng (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\REDO03.LOG'
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\LOG\REDO03.LOG'

--尝试切换当前日志组
--失败：数据库没有open
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-01109: database not open

恢复以前的数据文件

-- 关闭数据
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

-- ===删除文件 + 使用用以前的备份文件====

-- 启动数据库到 mount状态
SQL> startup mount
ORACLE instance started.

Total System Global Area 1319546880 bytes
Fixed Size                  2114656 bytes
Variable Size             503319456 bytes
Database Buffers          805306368 bytes
Redo Buffers                8806400 bytes
Database mounted.

-- 查看checkpoint_change#
SQL> select file#, checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2839508
         2            2839508
         3            2839508
         4            2839508
         5            2839508

SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2782605
         2            2782605
         3            2782605
         4            2782605
         5            2782605

-- 启动数据库 open，报错
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\WPENG\WPENG\SYSTEM01.DBF'

-- recover database until cancel
SQL> recover database
ORA-00279: change 2782605 generated at 09/27/2012 10:09:32 needed for thread 1
ORA-00289: suggestion : E:\APP\WPENG\PRODUCT\11.1.0\FLASH_RECOVER_AREA\WPENG\ARCHIVELOG\2012_09_27\O1_MF_1_118_867GK3OW_
.ARC
ORA-00280: change 2782605 for thread 1 is in sequence #118

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

ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\LOG\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-01112: media recovery not started

-- group 3 member删除，只能cancel
SQL> recover database until cancel
ORA-00279: change 2839398 generated at 10/15/2012 09:17:16 needed for thread 1
ORA-00289: suggestion : E:\APP\WPENG\PRODUCT\11.1.0\FLASH_RECOVER_AREA\WPENG\ARCHIVELOG\2012_10_15\O1_MF_1_138_%U_.ARC
ORA-00280: change 2839398 for thread 1 is in sequence #138

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

-- resetlogs方式open database
SQL> alter database open resetlogs;
Database altered.

-- ====删除的member创建由系统创建===

-- 5 commit的数据，在当前group 3，由于被误删除，丢失，不完全恢复
SQL> select * from t;
        ID
----------
         1
         2
         3
         4

-- 由于resetlogs方式打开数据库，所以log的sequence#重新开始了
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------

         1          1          1   52428800          2 YES ACTIVE                 2839399 15-OCT-12
         2          1          2   52428800          2 YES ACTIVE                 2839935 15-OCT-12
         3          1          3   52428800          2 NO  CURRENT                2839937 15-OCT-12

posted on 2012-10-14 23:06 盐城小土包阅读(232) 评论(0) 编辑收藏

新用户注册刷新评论列表


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻 Chat2DB C++博客博问管理

不完全恢复 - redo损坏

导航

统计

常用链接

留言簿

随笔档案(14)

文章分类(18)

文章档案(18)

搜索

最新评论

阅读排行榜

评论排行榜