常规恢复_不完全恢复_丢失redo log

注意事项：

1.确认丢失redo log后，切记不要关闭数据库，这样还有机会不丢失数据。

2.实例在线时，如果丢失的是archived状态的redo log，可以使用alter database clear logfile group 1;

3.实例在线时，如果丢失的是unarchived状态的redo log，可以使用alter database clear unarchived logfile group 1；

4.恢复之前先查询丢失redo log的状态，select group#,archived,status,members from v$log;

5.add drop logfile之后，要alter system switch logfile 后才能重用新建的redo logfile

6.在alert日志中，也会写当前使用的日志组是哪一组

7.可能的情况是：断电后或者存储丢失导致redo log需要恢复

8.实例在线时，只要使用clear 或clear archived logfile
实例关闭时，inactive可以使用clear ,active和current要使用recover database until cancel；alter database open resetlogs;

情况一：数据库关闭状态下丢失redo log

1.1.丢失一路redo log（非当前日志成员损坏），开启时没有报错

SQL> startup

ORA-15012: ASM file ‘+DATA/ghyyk/onlinelog/group_4.261.1033499459’ does not exist
Errors in file /u01/app/oracle/diag/rdbms/ghyyk/ghyyk/trace/ghyyk_lgwr_6825.trc:
ORA-00321: log 4 of thread 1, cannot update log file header
ORA-00312: online log 4 thread 1: ‘+DATA/ghyyk/onlinelog/group_4.261.1033499459’
日志报错

select group#,archived,status,members from v$log;SQL>selectstatus,memberfromv$logfile;
SYS@ghyyk> alter database drop logfile member ‘+DATA/ghyyk/onlinelog/group_4.261.1033499459’;
SYS@ghyyk> alter database add logfile member ‘+DATA’ to group 4;

1.2.丢失一路redo log（当前日志成员损坏），开启时没有报错

startup

Errors in file /u01/app/oracle/diag/rdbms/ghyyk/ghyyk/trace/ghyyk_m000_6534.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘+DATA/ghyyk/onlinelog/group_1.272.1033499455’
ORA-17503: ksfdopn:2 Failed to open file +DATA/ghyyk/onlinelog/group_1.272.1033499455
ORA-15012: ASM file ‘+DATA/ghyyk/onlinelog/group_1.272.1033499455’ does not exist
日志报错

select group#,archived,status,members from v$log;

SYS@ghyyk> alter database drop logfile member ‘+DATA/ghyyk/onlinelog/group_1.272.1033499455’;
alter database drop logfile member ‘+DATA/ghyyk/onlinelog/group_1.272.1033499455’
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1:
‘+DATA/ghyyk/onlinelog/group_1.272.1033499455’
ORA-00312: online log 1 thread 1: ‘+FRA/ghyyk/onlinelog/group_1.305.1033499455’

SYS@ test11g> alter system switch logfile;
System altered.

SYS@ghyyk> alter database drop logfile member ‘+DATA/ghyyk/onlinelog/group_1.272.1033499455’;

SYS@ test11g> alter database add logfile member ‘+DATA’ to group 1;
Database altered.

1.3丢失所有路径下的redo log，开启时报错

select group#,archived,status,members from v$log;
–非当前日志组损坏

SQL> startup

ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: ‘+FRA/ghyyk/onlinelog/group_3.307.1033564159’
ORA-17503: ksfdopn:2 Failed to open file +FRA/ghyyk/onlinelog/group_3.307.1033564159
ORA-15012: ASM file ‘+FRA/ghyyk/onlinelog/group_3.307.1033564159’ does not exist
ORA-00312: online log 3 thread 1: ‘+DATA/ghyyk/onlinelog/group_3.260.1033564159’
ORA-17503: ksfdopn:2 Failed to open file +DATA/ghyyk/onlinelog/group_3.260.1033564159
ORA-15012: ASM file ‘+DATA/ghyyk/onlinelog/group_3.260.1033564159’ does not exist

日志报错

SQL> startup mount;
SQL> select group#,archived,status,members from v$log;
SQL> alter database clear logfile group 3;
SQL> alter database open;

SYS@ghyyk> select group#,archived,status,members from v$log;
GROUP# ARC STATUS MEMBERS
1 NO CURRENT 2
2 YES INACTIVE 2
3 YES UNUSED 2
4 YES INACTIVE 2
clear后3号redo log变为unused状态

1.4 所有路径下的redo log都丢失，启动数据库报错：（不完全恢复）

select group#,archived,status,members from v$log;
–当前日志组损坏（CURRENT）

SQL> startup
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 939527288 bytes
Database Buffers 671088640 bytes
Redo Buffers 7245824 bytes
Database mounted.

ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: ‘+DATA/ghyyk/onlinelog/group_4.261.1033563397’
ORA-17503: ksfdopn:2 Failed to open file +DATA/ghyyk/onlinelog/group_4.261.1033563397
ORA-15012: ASM file ‘+DATA/ghyyk/onlinelog/group_4.261.1033563397’ does not exist
ORA-00312: online log 4 thread 1: ‘+FRA/ghyyk/onlinelog/group_4.308.1033499459’
ORA-17503: ksfdopn:2 Failed to open file +FRA/ghyyk/onlinelog/group_4.308.1033499459
ORA-15012: ASM file ‘+FRA/ghyyk/onlinelog/group_4.308.1033499459’ does not exist
日志报错

恢复方法：

SQL> shutdown immediate
SQL> startup mount
SQL> recover database until cancel;
SQL> alter database open resetlogs;

ASMCMD> ls
group_1.272.1033499455
group_2.271.1033499455
group_3.260.1033499457
group_4.261.1033499459

立即做一次全库备份

情况二：数据库打开状态下丢失redo log

2.1.查看日志组状态，是否归档，根据丢失的是什么状态的redo再进行恢复。

select group#,archived,status,members from vKaTeX parse error: Expected 'EOF', got '#' at position 18: …g; select group#̲,member from vlogfile;

SYS@ghyyk> select group#,archived,status,members from v$log;

GROUP# ARC STATUS MEMBERS
1 NO CURRENT 2
2 YES INACTIVE 2
3 YES INACTIVE 2
4 YES INACTIVE 2

在归档模式下：
1）丢失inactive redolog

1.redo log 状态是未归档
alter database clear unarchived logfile group 1;

2.已归档
alter database clear logfile group 1;
（实例在线和关闭都可以执行，都不会丢失数据）
（实例会自动在logfile路径下，重新创建logfile）

2）丢失active redolog

1.redo log 状态是未归档
a. 实例在线时损坏
alter database clear unarchived logfile group 1;
（在线执行不会丢失数据，但是千万不能关库执行）
b. 实例不正常关闭后损坏
startup nomount
alter system set “_allow_resetlogs_corruption”=true scope=spfile;
shutdown immediate
startup mount
recover database until cancel;
(会丢失数据)

2.redo log 状态是已归档
a.实例在线时损坏
alter database clear unarchived logfile group 1;
(直接在线执行，不会丢失数据)
b.实例不正常关闭后损坏
startup nomount
alter system set “_allow_resetlogs_corruption”=true scope=spfile;
shutdown immediate
startup mount
recover database until cancel;
alter database open resetlogs;
（不会丢失数据）

3）丢失current redolog

1.redo log 状态是未归档
a. 实例在线时损坏
alter system switch logfile;
alter system clear unarchived logfile group 1;
(直接在线执行，不会丢失数据)

b.redo log状态是未归档 不正常关闭数据库时 还有脏块没有写入数据文件时损坏
（会丢失数据）

丢失current redolog状态是未归档 并且已经不正常关闭数据库时 (不完全恢复）
具体恢复方法：

1、数据库基本信息和redo情况
SQL> show parameter db_name
NAME TYPE VALUE

db_name string leonliao
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/oradata/leonliao/arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9

SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
1 1 9 52428800 512 1 NO CURRENT 1250771 07-MAR-16 2.8147E+14
2 1 8 52428800 512 1 YES INACTIVE 1250768 07-MAR-16 1250771 07-MAR-16
3 1 7 52428800 512 1 YES INACTIVE 1250765 07-MAR-16 1250768 07-MAR-16

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
3 ONLINE /home/oracle/oradata/leonliao/redo03.log NO
2 ONLINE /home/oracle/oradata/leonliao/redo02.log NO
1 ONLINE /home/oracle/oradata/leonliao/redo01.log NO

2、在t_redo表插入一条记录2，并shutdown abort关闭数据库
SQL> select * from t_redo;
ID
1

SQL> insert into t_redo values(2);
1 row created.

SQL> commit;
Commit complete.

SQL> shutdown abort
ORACLE instance shut down.

3、删掉current 的redo文件
[oracle@leon1 leonliao]$ pwd
/home/oracle/oradata/leonliao
[oracle@leon1 leonliao]$ rm -rf redo01.log

4、启动数据库到mount状态并尝试打开数据库
SQL> startup mount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 432013312 bytes
Redo Buffers 7532544 bytes
Database mounted.

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: ‘/home/oracle/redo3a.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: ‘/home/oracle/redo3b.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SYS@ghyyk> select group#,archived,status,members from v$log;
（检查丢失文件状态）

SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance ghyyk (thread 1)
ORA-00312: online log 3 thread 1: ‘/home/oracle/redo3b.log’
ORA-00312: online log 3 thread 1: ‘/home/oracle/redo3a.log’

SQL> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1)
ORA-00312: online log 1 thread 1: ‘/home/oracle/oradata/leonliao/redo01.log’

SQL> recover database until cancel;
ORA-00279: change 1250771 generated at 03/07/2016 07:35:44 needed for thread 1
ORA-00289: suggestion : /home/oracle/oradata/leonliao/arch/1_9_905840705.dbf
ORA-00280: change 1250771 for thread 1 is in sequence #9

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1386552 generated at 02/28/2020 14:16:32 needed for thread 1
ORA-00289: suggestion :
+FRA/ghyyk/archivelog/2020_02_28/thread_1_seq_54.298.1033568205
ORA-00280: change 1386552 for thread 1 is in sequence #54
ORA-00278: log file
‘+FRA/ghyyk/archivelog/2020_02_28/thread_1_seq_53.301.1033568193’ no longer
needed for this recovery

ORA-00279: change 1386558 generated at 02/28/2020 14:16:44 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1386558 for thread 1 is in sequence #55
ORA-00278: log file
‘+FRA/ghyyk/archivelog/2020_02_28/thread_1_seq_54.298.1033568205’ no longer
needed for this recovery

ORA-00308: cannot open archived log ‘+FRA’
ORA-17503: ksfdopn:2 Failed to open file +FRA
ORA-15045: ASM file name ‘+FRA’ is not in reference form

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘+DATA/ghyyk/datafile/system.259.1033392089’

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/home/oracle/oradata/leonliao/system01.dbf’
(有可能会直接打开)

sys@PROD> select file#,checkpoint_change# from vKaTeX parse error: Expected 'EOF', got '#' at position 32: …OD> select file#̲,checkpoint_cha…datafile_header;

若打不开：
5、设置隐含参数_allow_resetlogs_corruption(打开数据库时跳过一致性检查，不一致的打开数据库非紧急情况不能使用，打开数据库后一定要进行数据库重建)为true
SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;

注意处理完毕后及时改回这个隐藏参数为false：
alter system set “_allow_resetlogs_corruption” = false scope=spfile;

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 432013312 bytes
Redo Buffers 7532544 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

6、验证数据是否丢失，数据为2的记录已经丢失