[oracle@enmotech ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.7 (Maipo)
資料庫版本
[oracle@enmotech ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 5月 31 10:23:55 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
連接配接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
redo0日志的狀态 列有四種狀态：
current: 目前日志組對應的髒塊還沒有全部從 data buffer 寫入到 data file，含有執行個體恢複需要的資訊，不能被覆寫
active: 日志組對應的髒塊還沒有完全從 data buffer 寫入到 data file，含有執行個體恢複需要的資訊，不能被覆寫
inactive; 日志組對應的髒塊已經從 data buffer 寫入到 data file，可以覆寫
unused: 新添加的日志組，還沒有使用
redo檔案損壞涉及到多種多樣場景，具體場景可以分四大部分：
1、按照redo的狀态可以分為current、active和inactive；
2、按照資料庫歸檔模式可以分為歸檔和非歸檔；
3、按照髒塊有沒寫入資料檔案可以分為需要和不需要；
4、按照損壞時資料庫的狀态可以分為線上和關閉；
現在主要通過三部分來介紹redo檔案恢複相關的内容：
1、current redo檔案恢複介紹：
1.1
a、current redo檔案損壞；
b、資料庫歸檔模式；
c、不需要寫入資料檔案
d、執行個體正常關閉後損壞
場景模拟；
1.檢視目前日志狀态
SYS@orcl> col member for a30;
SYS@orcl> select l.group#, l.sequence#, lf.member, l.bytes/1024/1024 M, l.archived, l.status from v$log l, v$logfile lf where l.group#=lf.group#;
GROUP# SEQUENCE# MEMBER M ARC STATUS
3 18 /u02/oradata/ORCL/redo03.log 200 NO CURRENT
2 17 /u02/oradata/ORCL/redo02.log 200 YES INACTIVE
1 16 /u02/oradata/ORCL/redo01.log 200 YES INACTIVE 2.關閉資料庫，模拟current redolog損壞
[oracle@enmotech ORCL]$ echo 1 >> /u02/oradata/ORCL/redo03.log
3.打開資料庫
ORACLE 例程已經啟動。
Total System Global Area 1543500832 bytes
Fixed Size 9135136 bytes
Variable Size 989855744 bytes
Database Buffers 536870912 bytes
Redo Buffers 7639040 bytes
資料庫裝載完畢。
ORA-03113: 通信通道的檔案結尾 程序 ID:
3845
會話 ID: 237 序列号: 62252
4.觀察alter_sid.log報錯
2020-05-31T11:05:52.415611+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_3804.trc:
ORA-00313: ??????? 3 (???? 1) ???
ORA-00312: ???? 3 ?? 1: '/u02/oradata/ORCL/redo03.log'
ORA-27046: ??????????????
Additional information: 1
2020-05-31T11:05:52.415707+08:00
2020-05-31T11:05:52.416332+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3845.trc:
ORA-00313: 無法打開日志組 1 (用于線程 ) 的成員
ORA-00312: 聯機日志 3 線程 1: '/u02/oradata/ORCL/redo03.log'
USER (ospid: ): terminating the instance due to ORA error
2020-05-31T11:05:52.533550+08:00
System state dump requested by (instance=1, osid=3845), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_3786.trc
2020-05-31T11:05:52.641053+08:00
Dumping diagnostic data in directory=[cdmp_20200531110552], requested by (instance=1, osid=3845), summary=[abnormal instance termination].
5.重新連接配接資料庫，清除目前日志組資料
SYS@orcl> alter database clear unarchived logfile group 3;
資料庫已更改。
6.打開資料庫
SYS@orcl> alter database open;
1.2
b、資料庫非歸檔模式；
場景模拟：
1.常看資料庫歸檔狀态和目前日志組
SYS@orcl> archive log list;
資料庫日志模式 非存檔模式
自動存檔 禁用
存檔終點 /u02/oradata/orcl_arch_log
最早的聯機日志序列 17
目前日志序列 19
SYS@orcl> col member for a40;
3 0 /u02/oradata/ORCL/redo03.log 200 YES UNUSED
2 17 /u02/oradata/ORCL/redo02.log 200 YES INACTIVE
1 19 /u02/oradata/ORCL/redo01.log 200 NO CURRENT SYS@orcl> shu immediate;
資料庫已經關閉。
已經解除安裝資料庫。
[oracle@enmotech ORCL]$ echo 1 >> /u02/oradata/ORCL/redo01.log
SYS@orcl> startup
5619
會話 ID: 237 序列号: 61537
2020-05-31T11:23:05.637817+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_5575.trc:
ORA-00313: ??????? 1 (???? 1) ???
ORA-00312: ???? 1 ?? 1: '/u02/oradata/ORCL/redo01.log'
2020-05-31T11:23:05.638267+08:00
2020-05-31T11:23:05.639749+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5619.trc:
ORA-00312: 聯機日志 1 線程 1: '/u02/oradata/ORCL/redo01.log'
2020-05-31T11:23:05.762325+08:00
System state dump requested by (instance=1, osid=5619), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_5558.trc
2020-05-31T11:23:05.848947+08:00
Dumping diagnostic data in directory=[cdmp_20200531112305], requested by (instance=1, osid=5619), summary=[abnormal instance termination].
2020-05-31T11:23:06.877329+08:00
Instance terminated by USER, pid = 5619
SYS@orcl> alter database clear logfile group 1;
連接配接scott使用者，建立test表，插入資料
[oracle@enmotech admin]$ sqlplus scott/tiger@orclpdb
SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 5月 31 11:38:59 2020
上次成功登入時間: 星期一 5月 18 2020 20:26:08 +08:00
SCOTT@orclpdb> create table test (id number);
表已建立。
SCOTT@orclpdb> insert into test(id) values (1);
已建立 1 行。
SCOTT@orclpdb> commit;
送出完成。
SCOTT@orclpdb> insert into test(id) values (2);
(插入資料2，不進行送出)
1.3
c、需要寫入資料檔案
d、執行個體線上current redo損壞
SYS@orcl> select l.group#, l.sequence#, lf.member, l.bytes/1024/1024 M, l.archived, l.status from v$log l, v$logfile lf where l.group#=lf.group#
3 20 /u02/oradata/ORCL/redo03.log 200 NO CURRENT
2 17 /u02/oradata/ORCL/redo02.log 200 YES INACTIVE
1 0 /u02/oradata/ORCL/redo01.log 200 YES UNUSED 2.模拟current redolog損壞
3.commit送出資料，觀察alter_sid.log報錯
2020-05-31T11:49:31.281843+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7598.trc:
ORA-00313: 無法打開日志組 3 (用于線程 1) 的成員
ORA-27046: 檔案大小不是邏輯塊大小的倍數
2020-05-31T11:49:31.281928+08:00
2020-05-31T11:49:31.320587+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_7880.trc:
4.清除目前日志組資料（一定不要關閉資料庫，避免資料丢失）
SYS@orcl> alter system switch logfile;
系統已更改。
5.打開資料庫
1.4
d、執行個體不正常關閉後current redo損壞
3 22 /u02/oradata/ORCL/redo03.log 200 NO CURRENT
2 17 /u02/oradata/ORCL/redo02.log 200 YES INACTIVE
1 0 /u02/oradata/ORCL/redo01.log 200 YES UNUSED | 2.強關資料庫 SYS@orcl> shutdown abort; ORACLE 例程已經關閉。 3.模拟current redolog損壞 [oracle@enmotech ~]$ echo 1 >> /u02/oradata/ORCL/redo03.log 4.打開資料庫 SYS@orcl> startup ORA-00313: 無法打開日志組 3 (用于線程 1) 的成員 ORA-00312: 聯機日志 3 線程 1: '/u02/oradata/ORCL/redo03.log' ORA-27046: 檔案大小不是邏輯塊大小的倍數 Additional information: 1 5.檢視告警日志 2020-05-31T12:12:03.310379+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9337.trc: 2020-05-31T12:12:03.362444+08:00 Slave encountered ORA-10388 exception during crash recovery 2020-05-31T12:12:03.375439+08:00 2020-05-31T12:12:03.378174+08:00 Aborting crash recovery due to error 313 2020-05-31T12:12:03.378309+08:00 2020-05-31T12:12:03.378837+08:00 ORA-313 signalled during: ALTER DATABASE OPEN... 2020-05-31T12:12:03.390997+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_9343.trc: 2020-05-31T12:12:03.476462+08:00 2020-05-31T12:12:03.552455+08:00 恢複資料 SYS@orcl> recover database until cancel; ORA-00279: 更改 2991918 (在 05/31/2020 12:02:08 生成) 對于線程 1 是必需的 ORA-00289: 建議: /u02/oradata/orcl_arch_log/1_22_1040302643.arc ORA-00280: 更改 2991918 (用于線程 1) 在序列 #22 中 指定日志: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: 無法打開歸檔日志 '/u02/oradata/orcl_arch_log/1_22_1040302643.arc' ORA-27037: 無法獲得檔案狀态 Linux-x86_64 Error: 2: No such file or directory Additional information: 7 ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出現如下錯誤 ORA-01194: 檔案 1 需要更多的恢複來保持一緻性 ORA-01110: 資料檔案 1: '/u02/oradata/ORCL/system01.dbf' SYS@orcl> alter database open resetlogs; alter database open resetlogs * 第 1 行出現錯誤: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], [] 程序 ID: 9733 會話 ID: 237 序列号: 18481 ERROR: ORA-03114: 未連接配接到 ORACLE SYS@orcl> quit 6.啟動資料庫到mount狀态 SYS@orcl> startup mount; 7.修改undo表空間 SYS@orcl> show parameter undo; NAME TYPE VALUE |
|---|
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@orcl> alter system set undo_management=manual scope=spfile;
SYS@orcl> alter system set undo_tablespace=system scope=spfile;
ORA-01109: 資料庫未打開
8.打開資料庫
1.5
3 18 /u02/oradata/ORCL/redo03.log 200 NO CURRENT
2 17 /u02/oradata/ORCL/redo02.log 200 YES INACTIVE
1 16 /u02/oradata/ORCL/redo01.log 200 YES INACTIVE 2.檢視告警日志
2020-05-31T13:31:10.075933+08:00
Thread 1 advanced to log sequence 19 (LGWR switch)
Current log# 1 seq# 19 mem# 0: /u02/oradata/ORCL/redo01.log
2020-05-31T13:31:22.970981+08:00
alter database clear logfile group 3
ORA-1624 signalled during: alter database clear logfile group 3...
2020-05-31T13:31:23.058733+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_4507.trc:
2020-05-31T13:31:23.257429+08:00
2020-05-31T13:31:23.334583+08:00
Checker run found 2 new persistent data failures
2020-05-31T13:32:52.559008+08:00
2020-05-31T13:32:52.559989+08:00
Clearing online log 3 of thread 1 sequence number 18
2020-05-31T13:32:52.562391+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4056.trc:
2020-05-31T13:32:52.562794+08:00
3.切換redo日志
4.歸檔目前的redo日志
SYS@orcl> alter system checkpoint;
5.清除歸檔日志
SYS@orcl> alter database clear logfile group 3;
6.檢視目前日志狀态
3 0 /u02/oradata/ORCL/redo03.log 200 NO UNUSED
2 17 /u02/oradata/ORCL/redo02.log 200 YES INACTIVE
1 19 /u02/oradata/ORCL/redo01.log 200 NO CURRENT 1.6
3 0 /u02/oradata/ORCL/redo03.log 200 NO UNUSED
2 17 /u02/oradata/ORCL/redo02.log 200 YES INACTIVE
1 19 /u02/oradata/ORCL/redo01.log 200 NO CURRENT | echo 1 >> /u02/oradata/ORCL/redo01.log 3.強關資料庫 4.恢複資料庫 SYS@orcl> startup nomount; SYS@orcl> alter system set "_allow_resetlogs_corruption"=true scope=spfile; ORA-01507: ?????? ORA-00279: 更改 2983632 (在 05/31/2020 13:32:44 生成) 對于線程 1 是必需的 ORA-00289: 建議: /u02/oradata/orcl_arch_log/1_19_1040302643.arc ORA-00280: 更改 2983632 (用于線程 1) 在序列 #19 中 | /u02/oradata/orcl_arch_log/1_19_1040302643.arc ORA-00308: 無法打開歸檔日志 '/u02/oradata/orcl_arch_log/1_19_1040302643.arc' | /u02/oradata/ORCL/redo01.log ORA-00308: 無法打開歸檔日志 '/u02/oradata/ORCL/redo01.log' | /u02/oradata/ORCL/redo02.log ORA-00310: 歸檔日志包含序列 17; 要求序列 19 ORA-00334: 歸檔日志: '/u02/oradata/ORCL/redo02.log' SYS@orcl> recover database using backup controlfile until cancel; | ORA-00600: internal error code, arguments: [ktcpoptx:!cmt top lvl], [], [], [], [], [], [], [], [], [], [], [] 程序 ID: 5296 會話 ID: 237 序列号: 10783 5.切換undo SQLPlus: Release 19.0.0.0.0 - Production on 星期日 5月 31 13:44:00 2020 已連接配接到空閑例程。 |
|---|