蜜桃视频在线免费,亚洲欧洲精品一区二区,久久精品夜色噜噜亚洲aⅴ

Oracle 在路上...

不完全恢復 - 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 鹽城小土包閱讀(239) 評論(0) 編輯收藏