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

最新推荐文章于 2022-10-02 12:03:57 发布
最新推荐文章于 2022-10-02 12:03:57 发布
阅读量554 收藏
点赞数
文章标签： oracle ogg 00324

今天，客户的一个测试环境，在启动数据库的时候，从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

赏

weixin_39943202
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle 00314,ORA-00314: log 1 of thread 1, expected sequence# doesn't match
weixin_33503186的博客
04-02 277
ORA-00314: log 1 of thread 1, expected sequence# doesn't match2018-08-10alter database clear logfile group 1;会清空redo01.logSQL>select name, log_mode from v$database;SQL>shutdown immediate;SQL&gt...
ORA-00314
cijinli4767的博客
06-09 191
Error: ORA-314 Text: log of thread , expected sequence # does not match --...
ORA-00314: LOG 404&nbs…
babymouse1212的博客
05-02 266
In this Document Symptoms Changes Cause Solution Applies to: Oracle Server - Enterprise Edition - Version 10.1.0.5 to 11.2.0.2 [Release 10.1 to 11.2] Information in t
ORA-00314,redolog损坏，或丢失处理方法
sustwct的专栏
01-03 1413
http://www.2cto.com/database/201309/246856.html
oracle 00314,ORA-00314,redolog 损坏，或丢失处理方法
weixin_32820131的博客
04-02 187
alertsid.log报错信息：Fri Sep 27 15:18:39 2013Started redo scanFri Sep 27 15:18:39 2013Errors in file /Oracle/admin/lixora/udump/lixora_ora_23439.trc:ORA-00314: log 2 of thread 1, expected sequence# 335 do...
连接Oracle数据库时报ORA-12541:TNS:无监听程序的图文解决教程
09-10
主要介绍了连接Oracle数据库时报ORA-12541:TNS:无监听程序的图文解决教程,非常不错，具有参考借鉴价值，需要的朋友可以参考下
Oracle dbca时报：ORA-12547: TNS:lost contact错误的解决
09-09
主要给大家介绍了关于Oracle在dbca时报：ORA-12547: TNS:lost contact错误的解决方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面来一起看看吧。
Oracle 11gr2连Oracle 19c 报ORA-28040 ORA-01017解决方法.pdf
07-16
Oracle 11gr2连Oracle 19c 报ORA-28040 ORA-01017解决方法
oracledb_exporter.0.3.0rc1-ora18.5.linux-amd64.tar.gz
06-17
1. **安装与配置**：下载`oracledb_exporter.0.3.0rc1-ora18.5.linux-amd64.tar.gz`压缩包，解压后设置环境变量和配置文件，包括数据库连接参数、暴露的端口和需要监控的指标。 2. **启动服务**：运行`oracledb_...
【故障解决】OGG-00446 Could not find archived log for sequence
anzhen0429的博客
02-24 1440
一.1  BLOG文档结构图   一.2  前言部分 一.2.1  导读和注意事项各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：① OGG-00446 Could not find archived log for sequence 的解决方法（重点）② OGG基本的维护、排错操作   Tips：        ① 若文章代码格式有错...
ORA-00314 ORA-00312
12-31 867
SQL> alter database open;alter database open*第 1 行出现错误:ORA-00314: 日志 1 (用于线程 1) 要求的 sequence# 与 不匹配ORA-00312:...
ORA-0103登陆错误 ORA-00314和ORA-00312错误 解决方案
a5115251的专栏
12-03 855
求各位高手帮帮忙 数据库在起到 startup后  出现以下保错信息  ORA-00314：log 3 of thread 1 ,expected sequence# doesn't match  ORA-00312:online log 3 thread 1 :'/home/oracle/app/oracle/oradata/ora8/redo01.log' 现在数据库起不来 很急啊 哪位能...
mq java是什么_java里面实现MQ的原理是什么？
weixin_42539973的博客
02-13 719
繁星点点滴滴一般数据的放和收对应的是2个线程或进程，以达到异步的目的使得吞吐量最大化。所以你说的是对的，一个不停的add数据，一个不停的get数据，但这样有一个问题，如果你数据添加过慢或过快，或者数据处理的过慢或过快，都会出现队列空或者队列满的情况，这种情况一旦出现，意味着一方要等另一方完成动作才能继续，降低吞吐量，所以一般是会有一个超时返回的。下面一个是无超时的消息队列getpublic Mes...
ora-16014;ora-00312 常见的问题
xiaomifeng287的专栏
01-30 554
今天早晨打开alter_XX.log  。看了一下日志，发现了两个错误，昨天刚使用rman 的delete archivelog all，delete obsolete backup，把控制文件里面的日志记录删除和备份过期的文件删除，这个错也是昨天产生的。就想总结一下处理过程：   使用 cat /XXX/XXX/XXX/XXX/bdump/alter_zdtdb.log|grep -i or
oracle 00314,ORA-00314和ORA-00312错误解决方法 | 信春哥，系统稳，闭眼上线不回滚！...
weixin_42358409的博客
04-02 888
之前BI的一个数据库磁盘故障，修复后由于没有足够空间的服务器和存储，使用了一台I/O很慢的存储临时搭建了DG，昨天新采购的服务器到货，要把备库迁移到新的服务器上。备库迁移有很多种方案可以选择，我选择了一种比较方便比较简单的方案，直接拷贝备库，因为这个备库只起到一个容灾的作用，并没有承载任何的业务。我的操作步骤是，停止备库的MRP进程(或者将数据库启动到MOUNT状态，不要启动MRP进程)，这样数据...
oracle停止集群服务器,oracle 11g 集群异常关闭
weixin_33685869的博客
04-03 565
本帖最后由 gh_95533 于 2018-4-16 10:28 编辑重启机器后，数据库自动启动，但是报了在新的日志中报错信息是Errors in file /home/u01/app/oracle/diag/rdbms/nbdb/nbdb1/trace/nbdb1_m000_10907.trc:ORA-19815: WARNING: db_recovery_file_dest_size of 4...
oracle数据库日志进程,ORACLE数据库db write进程及redo log不足导致minact-scn useg scan erroring out with error e12751错误 ...
weixin_39801991的博客
04-08 791
今天又遇到了db write进程不足，导致数据库缓慢的问题，同事在搭建这个数据库时，忘了告诉他修改db write进程的个数，测试人员在大量修改和迁入数据时，数据库突然没有响应，程序连接不上数据库。我在本地登录时，将近五分钟才登录到数据库，观察告警日志发现大量的cannot allocate new log提示，这说明redo log不足，导致日志无法切换，但最根本的原因还是db write进程不...
oracle cannot allocate new log,oracle 11g 日志问题Thread 1 cannot allocate new log,
weixin_33603377的博客
04-16 511
tail -100f alert*.logArchived Log entry 22067 added for thread 1 sequence 13501 ID 0xffffffffffe006b9 dest 1:Wed Dec 27 07:46:14 2017Thread 1 cannot allocate new log, sequence 13503Checkpoint not comp...
Oracle报错ORA-00603 ORA-00600[4193]错误 解决方案
ZSY老司机的博客
10-02 3656
5、删除原有的undo tablespace创建新的undo tablespace。根据MOS介绍，此故障一般和undo segment有关。1、在做了redo log当前日志组被破坏恢复的测试后。2、启动数据库后出现ORA-600 【4193】的错误。查看告警日志，数据库启动正常，问题解决！3、通过pfile启动Instance。6、关闭数据库，重新通过spfle启动。1、通过spfile生成pfile。2、编辑pfile文件。3、数据库被强制关闭。
cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
最新发布
09-27
cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor是一个数据库错误。它表示在连接描述符中请求的服务当前未被监听程序所知道。这通常是由于...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值