oracle 日志丢失,ORACLE 非归档模式下REDO日志丢失修复

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131SQL> select member from v$logfile;

/ora/app/oracle/oradata/orcl/redo03.log

/ora/app/oracle/oradata/orcl/redo02.log

/ora/app/oracle/oradata/orcl/redo01.log

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     10

Current log sequence           12

SQL> ho rm $ORACLE_BASE/oradata/orcl/redo*.log   --模拟日志文件丢失。

SQL> ho ls $ORACLE_BASE/oradata/orcl/redo*.log

ls: cannot access /ora/app/oracle/oradata/orcl/redo*.log: No such file or directory

SQL> startup force

ORACLE instance started.

Total System Global Area 1536602112 bytes

Fixed Size                  2213616 bytes

Variable Size             956303632 bytes

Database Buffers          570425344 bytes

Redo Buffers                7659520 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/ora/app/oracle/oradata/orcl/redo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> alter database open resetlogs;        --第一步

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database using backup controlfile; --第二步

ORA-00279: change 1384991 generated at 05/22/2016 20:05:08 needed for thread 1

ORA-00289: suggestion : /ora/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_22/o1_mf_1_12_%u_.arc

ORA-00280: change 1384991 for thread 1 is in sequence #12

Specify log: {=suggested | filename | AUTO | CANCEL}

--注意：非归档，什么也不做，直接回车。

ORA-00308: cannot open archived log '/ora/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_22/o1_mf_1_12_%u_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> alter database open resetlogs;          --第三步

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/ora/app/oracle/oradata/orcl/system01.dbf'

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.                            --第四步，使用隐藏参数，不做一致性检查启动数据库。

SQL> startup force mount;                   --第五步，重启到MOUNT状态下。

ORACLE instance started.

Total System Global Area 1536602112 bytes

Fixed Size                  2213616 bytes

Variable Size             956303632 bytes

Database Buffers          570425344 bytes

Redo Buffers                7659520 bytes

Database mounted.

SQL> alter database open;                    --第六步

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;          --第七步，一般情况下会正常恢复。

ERROR:

ORA-03114: not connected to ORACLE

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [1384999], [0], [1385016], [4194432], [], [], [], [], [], []

Process ID: 28621

Session ID: 125 Serial number: 5

--由于隐藏参数导致的600错误。

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 22 22:56:21 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select status from v$instance;        --检查状态，数据库关闭了。

select status from v$instance

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

SQL> startup mount;                           --再次启动

ORACLE instance started.

Total System Global Area 1536602112 bytes

Fixed Size                  2213616 bytes

Variable Size             956303632 bytes

Database Buffers          570425344 bytes

Redo Buffers                7659520 bytes

Database mounted.

SQL> alter database open;

Database altered.                               --启动成功。

SQL> ho ls $ORACLE_BASE/oradata/orcl/red*         --日志文件自动生成

/ora/app/oracle/oradata/orcl/redo01.log  /ora/app/oracle/oradata/orcl/redo02.log  /ora/app/oracle/oradata/orcl/redo03.log

SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';

System altered.                                --第八步，还原隐藏参数值。

SQL> startup force                              --第九步，重启数据库，使参数生效。