oracle ogg 00324,Oracle Error:ORA-00314: log 1 of thread 1, expected sequence# doesn’t match & ORA-...

今天,客户的一个测试环境,在启动数据库的时候,从mount到open的时候,遇到了如题所示的错误。

后来,将该错误解决了;因此,下面从一个正常的库的问题重现到问题解法,梳理一下该问题。

打开日志跟踪:

先打开日志跟踪:

Oracle PL/SQL

[root@oracle-automatic ~]# su - oracle

Last login: Thu Dec 13 14:36:21 CST 2018 on pts/2

[oracle@oracle-automatic ~]$

[oracle@oracle-automatic ~]$ cd $ORACLE_BASE/diag/rdbms

[oracle@oracle-automatic rdbms]$ ls

oggme orcl

[oracle@oracle-automatic rdbms]$ cd oggme/

[oracle@oracle-automatic oggme]$ ls

i_1.mif oggme

[oracle@oracle-automatic oggme]$ cd oggme/

[oracle@oracle-automatic oggme]$ ls

alert cdump hm incident incpkg ir lck log metadata metadata_dgif metadata_pv stage sweep trace

[oracle@oracle-automatic oggme]$

[oracle@oracle-automatic oggme]$ tail -f trace/alert_oggme.log

===========================================================

No patches have been applied

===========================================================

2018-12-13T14:07:44.641947+08:00

db_recovery_file_dest_size of 8016 MB is 7.62% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

2018-12-13T14:07:45.398233+08:00

Resize operation completed for file# 4, old size 61440K, new size 66560K

======================

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

[root@oracle-automatic~]#su-oracle

Lastlogin:ThuDec1314:36:21CST2018onpts/2

[oracle@oracle-automatic~]$

[oracle@oracle-automatic~]$cd$ORACLE_BASE/diag/rdbms

[oracle@oracle-automaticrdbms]$ls

oggmeorcl

[oracle@oracle-automaticrdbms]$cdoggme/

[oracle@oracle-automaticoggme]$ls

i_1.mifoggme

[oracle@oracle-automaticoggme]$cdoggme/

[oracle@oracle-automaticoggme]$ls

alertcdumphmincidentincpkgirlcklogmetadatametadata_dgifmetadata_pvstagesweeptrace

[oracle@oracle-automaticoggme]$

[oracle@oracle-automaticoggme]$tail-ftrace/alert_oggme.log

===========================================================

Nopatcheshavebeenapplied

===========================================================

2018-12-13T14:07:44.641947+08:00

db_recovery_file_dest_sizeof8016MBis7.62%used.Thisisa

user-specifiedlimitontheamountofspacethatwillbeusedbythis

databaseforrecovery-relatedfiles,anddoesnotreflecttheamountof

spaceavailableintheunderlyingfilesystemorASMdiskgroup.

2018-12-13T14:07:45.398233+08:00

Resizeoperationcompletedforfile#4,oldsize61440K,newsize66560K

======================

再看看我们当前的数据库:

Oracle PL/SQL

[oracle@oracle-automatic ~]$ ps -ef | grep pmon

oracle 4583 1 0 14:07 ? 00:00:01 ora_pmon_oggme

oracle 6646 6380 0 14:36 pts/2 00:00:00 grep --color=auto pmon

[oracle@oracle-automatic ~]$

[oracle@oracle-automatic ~]$ export ORACLE_SID=oggme

[oracle@oracle-automatic ~]$

[oracle@oracle-automatic ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 13 14:36:46 2018

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Current log sequence 1

SQL>

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 1 NO CURRENT

2 0 YES UNUSED

3 0 YES UNUSED

SQL>

SQL> col member for a72

SQL> set linesize 400

SQL>

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_ CON_ID

---------- ------- ------- ------------------------------------------------------------------------ --- ----------

3 ONLINE /oradata/OGGME/onlinelog/o1_mf_3_g13xmqn7_.log NO 0

3 ONLINE /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_3_g13xmsx8_.log YES 0

2 ONLINE /oradata/OGGME/onlinelog/o1_mf_2_g13xmqlr_.log NO 0

2 ONLINE /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_2_g13xmsw7_.log YES 0

1 ONLINE /oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log NO 0

1 ONLINE /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log YES 0

6 rows selected.

SQL>

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

[oracle@oracle-automatic~]$ps-ef|greppmon

oracle45831014:07?00:00:01ora_pmon_oggme

oracle66466380014:36pts/200:00:00grep--color=auto pmon

[oracle@oracle-automatic~]$

[oracle@oracle-automatic~]$exportORACLE_SID=oggme

[oracle@oracle-automatic~]$

[oracle@oracle-automatic~]$sqlplus/assysdba

SQL*Plus:Release12.2.0.1.0ProductiononThuDec1314:36:462018

Copyright(c)1982,2016,Oracle.Allrightsreserved.

Connectedto:

OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProduction

SQL>

SQL>archiveloglist;

DatabaselogmodeNoArchiveMode

AutomaticarchivalDisabled

ArchivedestinationUSE_DB_RECOVERY_FILE_DEST

Oldestonlinelogsequence1

Currentlogsequence1

SQL>

SQL>selectgroup#,sequence#,archived,statusfromv$log;

GROUP#SEQUENCE#ARCSTATUS

---------- ---------- --- ----------------

11NOCURRENT

20YESUNUSED

30YESUNUSED

SQL>

SQL>colmemberfora72

SQL>setlinesize400

SQL>

SQL>select*fromv$logfile;

GROUP#STATUSTYPEMEMBERIS_CON_ID

---------- ------- ------- ------------------------------------------------------------------------ --- ----------

3ONLINE/oradata/OGGME/onlinelog/o1_mf_3_g13xmqn7_.logNO0

3ONLINE/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_3_g13xmsx8_.logYES0

2ONLINE/oradata/OGGME/onlinelog/o1_mf_2_g13xmqlr_.logNO0

2ONLINE/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_2_g13xmsw7_.logYES0

1ONLINE/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.logNO0

1ONLINE/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.logYES0

6rowsselected.

SQL>

可以看到:

1. 当前数据库是【非归档】

2. 组1,是当前组,其他的两个暂时没有使用

做切换,将其他两个组用起来:

Oracle PL/SQL

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 1 NO CURRENT

2 0 YES UNUSED

3 0 YES UNUSED

SQL>

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 1 NO ACTIVE

2 2 NO CURRENT

3 0 YES UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 1 NO INACTIVE

2 2 NO ACTIVE

3 3 NO CURRENT

SQL>

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

SQL>selectgroup#,sequence#,archived,statusfromv$log;

GROUP#SEQUENCE#ARCSTATUS

---------- ---------- --- ----------------

11NOCURRENT

20YESUNUSED

30YESUNUSED

SQL>

SQL>altersystemswitchlogfile;

Systemaltered.

SQL>selectgroup#,sequence#,archived,statusfromv$log;

GROUP#SEQUENCE#ARCSTATUS

---------- ---------- --- ----------------

11NOACTIVE

22NOCURRENT

30YESUNUSED

SQL>altersystemswitchlogfile;

Systemaltered.

SQL>selectgroup#,sequence#,archived,statusfromv$log;

GROUP#SEQUENCE#ARCSTATUS

---------- ---------- --- ----------------

11NOINACTIVE

22NOACTIVE

33NOCURRENT

SQL>

因为是非归档,所以ARC都是NO。

日志切换的日志:

Oracle PL/SQL

2018-12-13T14:47:32.456480+08:00

Thread 1 advanced to log sequence 2 (LGWR switch)

Current log# 2 seq# 2 mem# 0: /oradata/OGGME/onlinelog/o1_mf_2_g13xmqlr_.log

Current log# 2 seq# 2 mem# 1: /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_2_g13xmsw7_.log

2018-12-13T14:47:53.336283+08:00

Thread 1 advanced to log sequence 3 (LGWR switch)

Current log# 3 seq# 3 mem# 0: /oradata/OGGME/onlinelog/o1_mf_3_g13xmqn7_.log

Current log# 3 seq# 3 mem# 1: /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_3_g13xmsx8_.log

1

2

3

4

5

6

7

8

2018-12-13T14:47:32.456480+08:00

Thread1advancedtologsequence2(LGWRswitch)

Currentlog#2seq#2mem#0:/oradata/OGGME/onlinelog/o1_mf_2_g13xmqlr_.log

Currentlog#2seq#2mem#1:/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_2_g13xmsw7_.log

2018-12-13T14:47:53.336283+08:00

Thread1advancedtologsequence3(LGWRswitch)

Currentlog#3seq#3mem#0:/oradata/OGGME/onlinelog/o1_mf_3_g13xmqn7_.log

Currentlog#3seq#3mem#1:/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_3_g13xmsx8_.log

下面关库,并开始破坏:

注意,破坏状态为INACTIVE的。

关库:

Oracle PL/SQL

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oracle-automatic ~]$

[oracle@oracle-automatic ~]$ ps -ef | grep ora_

oracle 22879 6380 0 14:50 pts/2 00:00:00 grep --color=auto ora_

[oracle@oracle-automatic ~]$

1

2

3

4

5

6

7

8

9

10

11

SQL>shutdownimmediate;

Databaseclosed.

Databasedismounted.

ORACLEinstanceshutdown.

SQL>

SQL>exit

DisconnectedfromOracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProduction

[oracle@oracle-automatic~]$

[oracle@oracle-automatic~]$ps-ef|grepora_

oracle228796380014:50pts/200:00:00grep--color=auto ora_

[oracle@oracle-automatic~]$

破坏:

上面INACTIVE的是组1

组1的成员是:

/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log

就它了:

Oracle PL/SQL

[oracle@oracle-automatic ~]$ cd /oradata/OGGME/onlinelog/

[oracle@oracle-automatic onlinelog]$ ls -ltr

total 614412

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:07 o1_mf_2_g13xmqlr_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:07 o1_mf_3_g13xmqn7_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:44 o1_mf_1_g13xmqjv_.log

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ mkdir /oradata/backup

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ cp /oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log /oradata/backup/

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ pwd

/oradata/OGGME/onlinelog

[oracle@oracle-automatic onlinelog]$ ls -ltr

total 614412

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_1_g13xmqjv_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_2_g13xmqlr_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:49 o1_mf_3_g13xmqn7_.log

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ echo "broken" >> /oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ ls -ltr

total 614412

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_2_g13xmqlr_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:49 o1_mf_3_g13xmqn7_.log

-rw-r----- 1 oracle oinstall 209715719 Dec 13 14:52 o1_mf_1_g13xmqjv_.log

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ echo "broken" >> /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log

[oracle@oracle-automatic onlinelog]$

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

[oracle@oracle-automatic~]$cd/oradata/OGGME/onlinelog/

[oracle@oracle-automaticonlinelog]$ls-ltr

total614412

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:07 o1_mf_2_g13xmqlr_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:07 o1_mf_3_g13xmqn7_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:44 o1_mf_1_g13xmqjv_.log

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$mkdir/oradata/backup

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$cp/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log/oradata/backup/

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$pwd

/oradata/OGGME/onlinelog

[oracle@oracle-automaticonlinelog]$ls-ltr

total614412

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_1_g13xmqjv_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_2_g13xmqlr_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:49 o1_mf_3_g13xmqn7_.log

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$echo"broken">>/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$ls-ltr

total614412

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_2_g13xmqlr_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:49 o1_mf_3_g13xmqn7_.log

-rw-r----- 1 oracle oinstall 209715719 Dec 13 14:52 o1_mf_1_g13xmqjv_.log

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$echo"broken">>/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log

[oracle@oracle-automaticonlinelog]$

然后,准备起库:

Oracle PL/SQL

SQL> startup

ORACLE instance started.

Total System Global Area 5049942016 bytes

Fixed Size 8631096 bytes

Variable Size 1191185608 bytes

Database Buffers 3841982464 bytes

Redo Buffers 8142848 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 31396

Session ID: 497 Serial number: 2356

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SQL>startup

ORACLEinstancestarted.

TotalSystemGlobalArea5049942016bytes

FixedSize8631096bytes

VariableSize1191185608bytes

DatabaseBuffers3841982464bytes

RedoBuffers8142848bytes

Databasemounted.

ORA-03113:end-of-fileoncommunicationchannel

ProcessID:31396

SessionID:497Serialnumber:2356

SQL>

日志:

Oracle PL/SQL

Completed: ALTER DATABASE MOUNT

2018-12-13T14:57:03.690517+08:00

ALTER DATABASE OPEN

Ping without log force is disabled:

instance mounted in exclusive mode.

Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED

Endian type of dictionary set to little

2018-12-13T14:57:03.731962+08:00

Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_31262.trc:

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

ORA-00312: online log 1 thread 1: '/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log'

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

2018-12-13T14:57:03.732294+08:00

Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_31262.trc:

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

ORA-00312: online log 1 thread 1: '/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log'

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

2018-12-13T14:57:03.733555+08:00

Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_ora_31396.trc:

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

ORA-00312: online log 1 thread 1: '/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log'

ORA-00312: online log 1 thread 1: '/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log'

2018-12-13T14:57:03.758542+08:00

TT00: Gap Manager starting (PID:31400)

2018-12-13T14:57:03.785064+08:00

USER (ospid: 31396): terminating the instance due to error 313

2018-12-13T14:57:04.178348+08:00

System state dump requested by (instance=1, osid=31396), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_diag_31238_20181213145704.trc

2018-12-13T14:57:05.513999+08:00

Dumping diagnostic data in directory=[cdmp_20181213145704], requested by (instance=1, osid=31396), summary=[abnormal instance termination].

2018-12-13T14:57:07.004875+08:00

Instance terminated by USER, pid = 31396

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

Completed:ALTERDATABASEMOUNT

2018-12-13T14:57:03.690517+08:00

ALTERDATABASEOPEN

Pingwithoutlogforceisdisabled:

instancemountedinexclusivemode.

BufferCacheFullDBCachingmodechangingfromFULLCACHINGDISABLEDtoFULLCACHINGENABLED

Endiantypeofdictionarysettolittle

2018-12-13T14:57:03.731962+08:00

Errorsinfile/u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_31262.trc:

ORA-00313:openfailedformembersofloggroup1ofthread1

ORA-00312:onlinelog1thread1:'/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log'

ORA-27046:filesizeisnotamultipleoflogicalblocksize

Additionalinformation:1

2018-12-13T14:57:03.732294+08:00

Errorsinfile/u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_31262.trc:

ORA-00313:openfailedformembersofloggroup1ofthread1

ORA-00312:onlinelog1thread1:'/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log'

ORA-27046:filesizeisnotamultipleoflogicalblocksize

Additionalinformation:1

2018-12-13T14:57:03.733555+08:00

Errorsinfile/u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_ora_31396.trc:

ORA-00313:openfailedformembersofloggroup1ofthread

ORA-00312:onlinelog1thread1:'/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log'

ORA-00312:onlinelog1thread1:'/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log'

2018-12-13T14:57:03.758542+08:00

TT00:GapManagerstarting(PID:31400)

2018-12-13T14:57:03.785064+08:00

USER(ospid:31396):terminatingtheinstanceduetoerror313

2018-12-13T14:57:04.178348+08:00

Systemstatedumprequestedby(instance=1,osid=31396),summary=[abnormalinstancetermination].

SystemStatedumpedtotracefile/u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_diag_31238_20181213145704.trc

2018-12-13T14:57:05.513999+08:00

Dumpingdiagnosticdataindirectory=[cdmp_20181213145704],requestedby(instance=1,osid=31396),summary=[abnormalinstancetermination].

2018-12-13T14:57:07.004875+08:00

InstanceterminatedbyUSER,pid=31396

这样破坏后,我们得到的是ORA-03113的错误,和我们预期的ORA-00314不符。

修复:

Oracle PL/SQL

SQL> startup mount;

ORACLE instance started.

Total System Global Area 5049942016 bytes

Fixed Size 8631096 bytes

Variable Size 1191185608 bytes

Database Buffers 3841982464 bytes

Redo Buffers 8142848 bytes

Database mounted.

SQL>

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 1 NO INACTIVE

3 3 NO CURRENT

2 2 NO INACTIVE

SQL>

SQL> alter database clear logfile group 1;

Database altered.

SQL>

SQL> alter database clear logfile group 2;

Database altered.

SQL>

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 0 NO UNUSED

3 3 NO CURRENT

2 0 NO UNUSED

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE

--------- ----------------

OGGME PRIMARY

SQL>

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

oggme OPEN

SQL>

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

SQL>startupmount;

ORACLEinstancestarted.

TotalSystemGlobalArea5049942016bytes

FixedSize8631096bytes

VariableSize1191185608bytes

DatabaseBuffers3841982464bytes

RedoBuffers8142848bytes

Databasemounted.

SQL>

SQL>selectgroup#,sequence#,archived,statusfromv$log;

GROUP#SEQUENCE#ARCSTATUS

---------- ---------- --- ----------------

11NOINACTIVE

33NOCURRENT

22NOINACTIVE

SQL>

SQL>alterdatabaseclearlogfilegroup1;

Databasealtered.

SQL>

SQL>alterdatabaseclearlogfilegroup2;

Databasealtered.

SQL>

SQL>selectgroup#,sequence#,archived,statusfromv$log;

GROUP#SEQUENCE#ARCSTATUS

---------- ---------- --- ----------------

10NOUNUSED

33NOCURRENT

20NOUNUSED

SQL>

SQL>alterdatabaseopen;

Databasealtered.

SQL>

SQL>selectname,database_rolefromv$database;

NAMEDATABASE_ROLE

--------- ----------------

OGGMEPRIMARY

SQL>

SQL>selectinstance_name,statusfromv$instance;

INSTANCE_NAMESTATUS

---------------- ------------

oggmeOPEN

SQL>

清理redolog的时候的日志:

Oracle PL/SQL

2018-12-13T15:09:16.296825+08:00

alter database clear logfile group 1

2018-12-13T15:09:16.344276+08:00

Clearing online log 1 of thread 1 sequence number 1

2018-12-13T15:09:16.346274+08:00

Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_ora_12595.trc:

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

2018-12-13T15:09:16.828893+08:00

Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_m000_13589.trc:

ORA-00312: online log 1 thread 1: '/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log'

2018-12-13T15:09:17.000217+08:00

Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_m000_13589.trc:

ORA-00312: online log 1 thread 1: '/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log'

ORA-00316: log 1 of thread 1, type 0 in header is not log file

ORA-00312: online log 1 thread 1: '/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log'

Checker run found 2 new persistent data failures

2018-12-13T15:09:20.367827+08:00

Completed: alter database clear logfile group 1

2018-12-13T15:09:36.722299+08:00

alter database clear logfile group 2

2018-12-13T15:09:36.723037+08:00

Clearing online log 2 of thread 1 sequence number 2

2018-12-13T15:09:45.547008+08:00

Completed: alter database clear logfile group 2

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

2018-12-13T15:09:16.296825+08:00

alterdatabaseclearlogfilegroup1

2018-12-13T15:09:16.344276+08:00

Clearingonlinelog1ofthread1sequencenumber1

2018-12-13T15:09:16.346274+08:00

Errorsinfile/u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_ora_12595.trc:

ORA-00313:openfailedformembersofloggroup1ofthread1

2018-12-13T15:09:16.828893+08:00

Errorsinfile/u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_m000_13589.trc:

ORA-00312:onlinelog1thread1:'/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log'

2018-12-13T15:09:17.000217+08:00

Errorsinfile/u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_m000_13589.trc:

ORA-00312:onlinelog1thread1:'/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log'

ORA-00316:log1ofthread1,type0inheaderisnotlogfile

ORA-00312:onlinelog1thread1:'/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log'

Checkerrunfound2newpersistentdatafailures

2018-12-13T15:09:20.367827+08:00

Completed:alterdatabaseclearlogfilegroup1

2018-12-13T15:09:36.722299+08:00

alterdatabaseclearlogfilegroup2

2018-12-13T15:09:36.723037+08:00

Clearingonlinelog2ofthread1sequencenumber2

2018-12-13T15:09:45.547008+08:00

Completed:alterdatabaseclearlogfilegroup2

这就修好了。

然后再来看模拟前面的ORA-00314的问题:

继续切:

Oracle PL/SQL

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 7 NO INACTIVE

2 8 NO CURRENT

3 6 NO INACTIVE

SQL>

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 10 NO CURRENT

2 8 NO ACTIVE

3 9 NO ACTIVE

SQL>

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

SQL>selectgroup#,sequence#,archived,statusfromv$log;

GROUP#SEQUENCE#ARCSTATUS

---------- ---------- --- ----------------

17NOINACTIVE

28NOCURRENT

36NOINACTIVE

SQL>

SQL>altersystemswitchlogfile;

Systemaltered.

SQL>altersystemswitchlogfile;

Systemaltered.

SQL>selectgroup#,sequence#,archived,statusfromv$log;

GROUP#SEQUENCE#ARCSTATUS

---------- ---------- --- ----------------

110NOCURRENT

28NOACTIVE

39NOACTIVE

SQL>

切到组1为CURRENT

然后停掉:

Oracle PL/SQL

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

1

2

3

4

5

SQL>shutdownimmediate;

Databaseclosed.

Databasedismounted.

ORACLEinstanceshutdown.

SQL>

用以前的一个版本破坏掉:

Oracle PL/SQL

[oracle@oracle-automatic onlinelog]$ pwd

/oradata/OGGME/onlinelog

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ ls -ltr

total 614412

-rw-r----- 1 oracle oinstall 209715712 Dec 13 15:17 o1_mf_3_g13xmqn7_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 15:18 o1_mf_1_g13xmqjv_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 15:18 o1_mf_2_g13xmqlr_.log

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ ls -ltr /oradata/backup/

total 204804

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:51 o1_mf_1_g13xmqjv_.log

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ cp /oradata/backup/o1_mf_1_g13xmqjv_.log .

[oracle@oracle-automatic onlinelog]$

[oracle@oracle-automatic onlinelog]$ cp /oradata/backup/o1_mf_1_g13xmqjv_.log /oradata/fast_recovery_area/oggme/OGGME/onlinelog/

[oracle@oracle-automatic onlinelog]$

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

[oracle@oracle-automaticonlinelog]$pwd

/oradata/OGGME/onlinelog

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$ls-ltr

total614412

-rw-r----- 1 oracle oinstall 209715712 Dec 13 15:17 o1_mf_3_g13xmqn7_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 15:18 o1_mf_1_g13xmqjv_.log

-rw-r----- 1 oracle oinstall 209715712 Dec 13 15:18 o1_mf_2_g13xmqlr_.log

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$ls-ltr/oradata/backup/

total204804

-rw-r----- 1 oracle oinstall 209715712 Dec 13 14:51 o1_mf_1_g13xmqjv_.log

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$cp/oradata/backup/o1_mf_1_g13xmqjv_.log.

[oracle@oracle-automaticonlinelog]$

[oracle@oracle-automaticonlinelog]$cp/oradata/backup/o1_mf_1_g13xmqjv_.log/oradata/fast_recovery_area/oggme/OGGME/onlinelog/

[oracle@oracle-automaticonlinelog]$

最后,还是能起来,数据库还是可以OPEN。

不过ALERT日志中会有痕迹。

日志:

Oracle PL/SQL

2018-12-13T15:27:22.375508+08:00

Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_3550.trc:

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

1

2

3

2018-12-13T15:27:22.375508+08:00

Errorsinfile/u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_3550.trc:

ORA-00313:openfailedformembersofloggroup1ofthread1

ORA-00314,暂时重现不出来。

但是解决方式,和上面是差不多的。

——————————

Done。

Post Views:

293

感谢支持

1ad46b77b4ebe0c73911f418908f2eda.png

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值