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