oracle日志状态为STALE案例分析

最新推荐文章于 2023-01-04 05:10:42 发布

csub31890

最新推荐文章于 2023-01-04 05:10:42 发布

阅读量508

点赞数

文章标签：数据库

$ 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日志组恢复正常。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29618264/viewspace-2139747/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29618264/viewspace-2139747/