原文地址:http://d-prototype.com/archives/5249
如题所示的问题发生在数据库的启动过程中,具体如下:
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
|
[oracle
@oradg2
dbs]$
sqlplus
/
as
sysdba
SQL
*Plus:
Release
11.2.0.4.0
Production
on
Sat
Jun
18
08:15:57
2016
Copyright
(c)
1982,
2013,
Oracle.
All
rights
reserved.
Connected
to:
Oracle
Database
11g
Enterprise
Edition
Release
11.2.0.4.0
-
64bit
Production
With
the
Partitioning,
OLAP,
Data
Mining
and
Real
Application
Testing
options
SQL
>
select
instance_name,status
from
v$
instance;
INSTANCE_NAME
STATUS
---------------- ------------
orcl
MOUNTED
SQL
>
alter
database
open
resetlogs;
alter
database
open
resetlogs
*
ERROR
at
line
1:
ORA
-00392:
log
3
of
thread
1
is
being
cleared,
operation
not
allowed
ORA
-00312:
online
log
3
thread
1:
'/oradata/orcl/redo03.log'
SQL
>
|
发生这个问题的时候,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
|
SQL
>
desc
v$
log;
Name
Null?
Type
----------------------------------------- -------- ----------------------------
GROUP#
NUMBER
THREAD#
NUMBER
SEQUENCE#
NUMBER
BYTES
NUMBER
BLOCKSIZE
NUMBER
MEMBERS
NUMBER
ARCHIVED
VARCHAR2(3)
STATUS
VARCHAR2(16)
FIRST_CHANGE#
NUMBER
FIRST_TIME
DATE
NEXT_CHANGE#
NUMBER
NEXT_TIME
DATE
SQL
>
SQL
>
select
group#,bytes
/1024
/1024
"MB",status
from
v$
log;
GROUP#
MB
STATUS
---------- ---------- ----------------
1
50
CLEARING
3
50
CLEARING_CURRENT
2
50
CLEARING
SQL
>
SQL
>
desc
v$
logfile;
Name
Null?
Type
----------------------------------------- -------- ----------------------------
GROUP#
NUMBER
STATUS
VARCHAR2(7)
TYPE
VARCHAR2(7)
MEMBER
VARCHAR2(513)
IS_RECOVERY_DEST_FILE
VARCHAR2(3)
SQL
>
SQL
>
col
member
for
a40
SQL
>
select
group#,
member
from
v$
logfile;
GROUP#
MEMBER
---------- ----------------------------------------
3
/oradata
/orcl
/redo03.
log
2
/oradata
/orcl
/redo02.
log
1
/oradata
/orcl
/redo01.
log
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
|
SQL
>
alter
database
clear
logfile
group
1;
Database
altered.
SQL
>
alter
database
clear
logfile
group
2;
Database
altered.
SQL
>
alter
database
clear
logfile
group
3;
Database
altered.
SQL
>
select
group#,bytes
/1024
/1024
"MB",status
from
v$
log;
GROUP#
MB
STATUS
---------- ---------- ----------------
1
50
UNUSED
3
50
CURRENT
2
50
UNUSED
SQL
>
SQL
>
select
instance_name,status
from
v$
instance;
INSTANCE_NAME
STATUS
---------------- ------------
orcl
MOUNTED
SQL
>
alter
database
open
resetlogs;
Database
altered.
SQL
>
select
instance_name,status
from
v$
instance;
INSTANCE_NAME
STATUS
---------------- ------------
orcl
OPEN
SQL
>
|
————————————————————————
Done。