oracle日誌狀態為STALE案例分析
$ ps -ef|grep pmon
oracle 4144 1 0 May 17 ? 0:49 ora_pmon_orcl1
oracle 16493 1 0 May 17 ? 1:32 ora_pmon_prod1
oracle 12147 12022 0 12:24:22 pts/1 0:00 grep pmon
$ export ORACLE_SID=prod1
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 20 12:24:45 2017
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> col member for a70
SQL> set lines 156 pages 156
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE /dev/vgdata/rlv_redo101 NO
2 STALE ONLINE /dev/vgdata/rlv_redo102 NO
5 ONLINE /dev/vgdata/rlv_redo103 NO
6 ONLINE /dev/vgdata/rlv_redo104 NO
7 ONLINE /dev/vgdata/rlv_redo105 NO
8 ONLINE /dev/vgdata/rlv_redo106 NO
9 ONLINE /dev/vgdata/rlv_redo107 NO
10 ONLINE /dev/vgdata/rlv_redo108 NO
3 ONLINE /dev/vgdata/rlv_redo201 NO
4 ONLINE /dev/vgdata/rlv_redo202 NO
11 ONLINE /dev/vgdata/rlv_redo203 NO
12 ONLINE /dev/vgdata/rlv_redo204 NO
13 ONLINE /dev/vgdata/rlv_redo205 NO
14 ONLINE /dev/vgdata/rlv_redo206 NO
15 ONLINE /dev/vgdata/rlv_redo207 NO
16 ONLINE /dev/vgdata/rlv_redo208 NO
16 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7825 1048576000 1 NO INACTIVE 1.5453E+13 16-MAY-17
2 1 7826 1069547520 1 NO INACTIVE 1.5453E+13 16-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO CURRENT 1.5456E+13 17-MAY-17
7 1 7821 1069547520 1 NO INACTIVE 1.5453E+13 14-MAY-17
8 1 7822 1069547520 1 NO INACTIVE 1.5453E+13 15-MAY-17
9 1 7823 1069547520 1 NO INACTIVE 1.5453E+13 15-MAY-17
10 1 7824 1069547520 1 NO INACTIVE 1.5453E+13 15-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> SELECT V2.GROUP#, MEMBER, V2.STATUS MEMBER_STATUS,
V1.STATUS GROUP_STATUS
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# AND V2.STATUS = 'STALE';
GROUP# MEMBER MEMBER_ GROUP_STATUS
---------- ---------------------------------------------------------------------- ------- ----------------
2 /dev/vgdata/rlv_redo102 STALE INACTIVE
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/10.2.0.1/db_1/dbs/arch
Oldest online log sequence 7822
Current log sequence 7829
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE /dev/vgdata/rlv_redo101 NO
2 STALE ONLINE /dev/vgdata/rlv_redo102 NO
5 ONLINE /dev/vgdata/rlv_redo103 NO
6 ONLINE /dev/vgdata/rlv_redo104 NO
7 ONLINE /dev/vgdata/rlv_redo105 NO
8 ONLINE /dev/vgdata/rlv_redo106 NO
9 ONLINE /dev/vgdata/rlv_redo107 NO
10 ONLINE /dev/vgdata/rlv_redo108 NO
3 ONLINE /dev/vgdata/rlv_redo201 NO
4 ONLINE /dev/vgdata/rlv_redo202 NO
11 ONLINE /dev/vgdata/rlv_redo203 NO
12 ONLINE /dev/vgdata/rlv_redo204 NO
13 ONLINE /dev/vgdata/rlv_redo205 NO
14 ONLINE /dev/vgdata/rlv_redo206 NO
15 ONLINE /dev/vgdata/rlv_redo207 NO
16 ONLINE /dev/vgdata/rlv_redo208 NO
16 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7825 1048576000 1 NO INACTIVE 1.5453E+13 16-MAY-17
2 1 7826 1069547520 1 NO INACTIVE 1.5453E+13 16-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO INACTIVE 1.5456E+13 17-MAY-17
7 1 7829 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
8 1 7830 1069547520 1 NO ACTIVE 1.5467E+13 20-MAY-17
9 1 7831 1069547520 1 NO ACTIVE 1.5467E+13 20-MAY-17
10 1 7832 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7825 1048576000 1 NO INACTIVE 1.5453E+13 16-MAY-17
2 1 7826 1069547520 1 NO INACTIVE 1.5453E+13 16-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO INACTIVE 1.5456E+13 17-MAY-17
7 1 7829 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
8 1 7830 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
9 1 7831 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
10 1 7832 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7833 1048576000 1 NO CURRENT 1.5467E+13 20-MAY-17
2 1 7826 1069547520 1 NO INACTIVE 1.5453E+13 16-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO INACTIVE 1.5456E+13 17-MAY-17
7 1 7829 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
8 1 7830 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
9 1 7831 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
10 1 7832 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7833 1048576000 1 NO INACTIVE 1.5467E+13 20-MAY-17
2 1 7834 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO INACTIVE 1.5456E+13 17-MAY-17
7 1 7829 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
8 1 7830 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
9 1 7831 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
10 1 7832 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> alter system checkpoint;
System altered.
SQL
SQL>
SQL> SELECT V2.GROUP#, MEMBER, V2.STATUS MEMBER_STATUS,
V1.STATUS GROUP_STATUS
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# AND V2.STATUS = 'STALE';
no rows selected
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE /dev/vgdata/rlv_redo101 NO
2 ONLINE /dev/vgdata/rlv_redo102 NO
5 ONLINE /dev/vgdata/rlv_redo103 NO
6 ONLINE /dev/vgdata/rlv_redo104 NO
7 ONLINE /dev/vgdata/rlv_redo105 NO
8 ONLINE /dev/vgdata/rlv_redo106 NO
9 ONLINE /dev/vgdata/rlv_redo107 NO
10 ONLINE /dev/vgdata/rlv_redo108 NO
3 ONLINE /dev/vgdata/rlv_redo201 NO
4 ONLINE /dev/vgdata/rlv_redo202 NO
11 ONLINE /dev/vgdata/rlv_redo203 NO
12 ONLINE /dev/vgdata/rlv_redo204 NO
13 ONLINE /dev/vgdata/rlv_redo205 NO
14 ONLINE /dev/vgdata/rlv_redo206 NO
15 ONLINE /dev/vgdata/rlv_redo207 NO
16 ONLINE /dev/vgdata/rlv_redo208 NO
16 rows selected.
資料庫巡檢發現日誌為stale狀態，解決思路：強制切換日誌歸檔（alter system switch logfile），重置檢查點（alter system checkpoint），當v$log日誌組的status切換到current時，v$logfile日誌組恢復正常。
oracle 4144 1 0 May 17 ? 0:49 ora_pmon_orcl1
oracle 16493 1 0 May 17 ? 1:32 ora_pmon_prod1
oracle 12147 12022 0 12:24:22 pts/1 0:00 grep pmon
$ export ORACLE_SID=prod1
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 20 12:24:45 2017
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> col member for a70
SQL> set lines 156 pages 156
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE /dev/vgdata/rlv_redo101 NO
2 STALE ONLINE /dev/vgdata/rlv_redo102 NO
5 ONLINE /dev/vgdata/rlv_redo103 NO
6 ONLINE /dev/vgdata/rlv_redo104 NO
7 ONLINE /dev/vgdata/rlv_redo105 NO
8 ONLINE /dev/vgdata/rlv_redo106 NO
9 ONLINE /dev/vgdata/rlv_redo107 NO
10 ONLINE /dev/vgdata/rlv_redo108 NO
3 ONLINE /dev/vgdata/rlv_redo201 NO
4 ONLINE /dev/vgdata/rlv_redo202 NO
11 ONLINE /dev/vgdata/rlv_redo203 NO
12 ONLINE /dev/vgdata/rlv_redo204 NO
13 ONLINE /dev/vgdata/rlv_redo205 NO
14 ONLINE /dev/vgdata/rlv_redo206 NO
15 ONLINE /dev/vgdata/rlv_redo207 NO
16 ONLINE /dev/vgdata/rlv_redo208 NO
16 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7825 1048576000 1 NO INACTIVE 1.5453E+13 16-MAY-17
2 1 7826 1069547520 1 NO INACTIVE 1.5453E+13 16-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO CURRENT 1.5456E+13 17-MAY-17
7 1 7821 1069547520 1 NO INACTIVE 1.5453E+13 14-MAY-17
8 1 7822 1069547520 1 NO INACTIVE 1.5453E+13 15-MAY-17
9 1 7823 1069547520 1 NO INACTIVE 1.5453E+13 15-MAY-17
10 1 7824 1069547520 1 NO INACTIVE 1.5453E+13 15-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> SELECT V2.GROUP#, MEMBER, V2.STATUS MEMBER_STATUS,
V1.STATUS GROUP_STATUS
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# AND V2.STATUS = 'STALE';
GROUP# MEMBER MEMBER_ GROUP_STATUS
---------- ---------------------------------------------------------------------- ------- ----------------
2 /dev/vgdata/rlv_redo102 STALE INACTIVE
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/10.2.0.1/db_1/dbs/arch
Oldest online log sequence 7822
Current log sequence 7829
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE /dev/vgdata/rlv_redo101 NO
2 STALE ONLINE /dev/vgdata/rlv_redo102 NO
5 ONLINE /dev/vgdata/rlv_redo103 NO
6 ONLINE /dev/vgdata/rlv_redo104 NO
7 ONLINE /dev/vgdata/rlv_redo105 NO
8 ONLINE /dev/vgdata/rlv_redo106 NO
9 ONLINE /dev/vgdata/rlv_redo107 NO
10 ONLINE /dev/vgdata/rlv_redo108 NO
3 ONLINE /dev/vgdata/rlv_redo201 NO
4 ONLINE /dev/vgdata/rlv_redo202 NO
11 ONLINE /dev/vgdata/rlv_redo203 NO
12 ONLINE /dev/vgdata/rlv_redo204 NO
13 ONLINE /dev/vgdata/rlv_redo205 NO
14 ONLINE /dev/vgdata/rlv_redo206 NO
15 ONLINE /dev/vgdata/rlv_redo207 NO
16 ONLINE /dev/vgdata/rlv_redo208 NO
16 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7825 1048576000 1 NO INACTIVE 1.5453E+13 16-MAY-17
2 1 7826 1069547520 1 NO INACTIVE 1.5453E+13 16-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO INACTIVE 1.5456E+13 17-MAY-17
7 1 7829 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
8 1 7830 1069547520 1 NO ACTIVE 1.5467E+13 20-MAY-17
9 1 7831 1069547520 1 NO ACTIVE 1.5467E+13 20-MAY-17
10 1 7832 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7825 1048576000 1 NO INACTIVE 1.5453E+13 16-MAY-17
2 1 7826 1069547520 1 NO INACTIVE 1.5453E+13 16-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO INACTIVE 1.5456E+13 17-MAY-17
7 1 7829 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
8 1 7830 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
9 1 7831 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
10 1 7832 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7833 1048576000 1 NO CURRENT 1.5467E+13 20-MAY-17
2 1 7826 1069547520 1 NO INACTIVE 1.5453E+13 16-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO INACTIVE 1.5456E+13 17-MAY-17
7 1 7829 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
8 1 7830 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
9 1 7831 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
10 1 7832 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7833 1048576000 1 NO INACTIVE 1.5467E+13 20-MAY-17
2 1 7834 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
3 2 12529 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
4 2 12530 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
5 1 7827 1069547520 1 NO INACTIVE 1.5455E+13 16-MAY-17
6 1 7828 1069547520 1 NO INACTIVE 1.5456E+13 17-MAY-17
7 1 7829 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
8 1 7830 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
9 1 7831 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
10 1 7832 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
11 2 12531 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
12 2 12532 1069547520 1 NO CURRENT 1.5467E+13 20-MAY-17
13 2 12525 1069547520 1 NO INACTIVE 1.5467E+13 19-MAY-17
14 2 12526 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
15 2 12527 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 2 12528 1069547520 1 NO INACTIVE 1.5467E+13 20-MAY-17
16 rows selected.
SQL> alter system checkpoint;
System altered.
SQL
SQL>
SQL> SELECT V2.GROUP#, MEMBER, V2.STATUS MEMBER_STATUS,
V1.STATUS GROUP_STATUS
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# AND V2.STATUS = 'STALE';
no rows selected
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE /dev/vgdata/rlv_redo101 NO
2 ONLINE /dev/vgdata/rlv_redo102 NO
5 ONLINE /dev/vgdata/rlv_redo103 NO
6 ONLINE /dev/vgdata/rlv_redo104 NO
7 ONLINE /dev/vgdata/rlv_redo105 NO
8 ONLINE /dev/vgdata/rlv_redo106 NO
9 ONLINE /dev/vgdata/rlv_redo107 NO
10 ONLINE /dev/vgdata/rlv_redo108 NO
3 ONLINE /dev/vgdata/rlv_redo201 NO
4 ONLINE /dev/vgdata/rlv_redo202 NO
11 ONLINE /dev/vgdata/rlv_redo203 NO
12 ONLINE /dev/vgdata/rlv_redo204 NO
13 ONLINE /dev/vgdata/rlv_redo205 NO
14 ONLINE /dev/vgdata/rlv_redo206 NO
15 ONLINE /dev/vgdata/rlv_redo207 NO
16 ONLINE /dev/vgdata/rlv_redo208 NO
16 rows selected.
資料庫巡檢發現日誌為stale狀態，解決思路：強制切換日誌歸檔（alter system switch logfile），重置檢查點（alter system checkpoint），當v$log日誌組的status切換到current時，v$logfile日誌組恢復正常。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29618264/viewspace-2139747/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 日誌的狀態
- 簡述oracle日誌組的四種狀態Oracle
- 【DataGuard】Oracle DataGuard 日誌傳輸狀態監控Oracle
- logminer 日誌分析案例
- 有關重做日誌的狀態及switch logfile時的不正常案例分析
- 13、oracle中的幾個狀態，invalid,obsolete,expired,staleOracle
- 談談Oracle日誌檔案的INVALID狀態Oracle
- 16、重做日誌檔案的狀態及重做日誌組的狀態說明
- VCS叢集狀態為 STALE_ADMIN_WAIT的解決AI
- 談談Oracle日誌檔案的INVALID狀態（上）Oracle
- 談談Oracle日誌檔案的INVALID狀態（下）Oracle
- 狀態為active的日誌組也是不允許刪除的
- 狀態為inactive的日誌一定歸檔了嗎？
- 使用logminer分析歸檔日誌案例
- oracle關閉狀態刪除活動日誌報錯恢復（一）Oracle
- 日誌分析-apache日誌分析Apache
- 【聽海日誌】之ORACLE恢復案例Oracle
- Oracle listener log 日誌分析方法Oracle
- 有條件分析oracle日誌Oracle
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- 終止oracle日誌分析事務Oracle
- 深入分析Oracle日誌檔案Oracle
- 網站日誌統計案例分析與實現網站
- oracle日誌分析從列表中移去一個日誌檔案Oracle
- Nginx日誌499502503504報錯狀態碼Nginx
- 為oracle新增重做日誌組及重做日誌成員Oracle
- [日誌分析篇]-利用ELK分析jumpserver日誌-日誌拆分篇Server
- materialized view的fast和日誌分析和一則案例ZedViewAST
- 轉載：使用Log Miner分析Oracle日誌Oracle
- logminer抽取日誌案例
- Oracle日誌Oracle
- 【Oracle日誌】- 日誌檔案重建Oracle
- 玄機-第二章日誌分析-apache日誌分析Apache
- Apche日誌系列(4)：日誌分析(轉)
- Vben Admin 原始碼學習:狀態管理-錯誤日誌原始碼
- 監聽狀態對dataguard及其日誌傳輸的影響
- inactive狀態日誌組檔案損壞的恢復
- FDOAGENT日誌分析