月归档：二月 2012

1.数据库版本

SQL> select * from v$version;

BANNER
-------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

2.alert日志出现类此记录

Fri Feb 10 05:16:55 2012
ARC1: Media recovery disabled
Fri Feb 10 05:17:23 2012
ARC0: Media recovery disabled
Fri Feb 10 05:17:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:18:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:19:12 2012
Thread 1 advanced to log sequence 24
  Current log# 1 seq# 24 mem# 0: /tmp/recover/redo01.log
Fri Feb 10 05:19:12 2012
ARC0: Media recovery disabled
Fri Feb 10 05:19:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:20:23 2012
ARC1: Media recovery disabled
Fri Feb 10 05:21:23 2012
ARC1: Media recovery disabled

3.数据库归档信息

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /tmp/recover
Oldest online log sequence     25
Current log sequence           27

SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
log_archive_start                    boolean     TRUE

从这里可以看出，数据库处于非归档模式，但是log_archive_start=true，这样会导致归档进程自动启动(ARCn),因为数据库设置为非归档模式，从而导致该进程启动后，又自己关闭

4.解决方法

SQL> alter system set log_archive_start=false scope=spfile;

System altered.

SQL> startup force      
ORACLE instance started.

Total System Global Area  202445176 bytes
Fixed Size                   450936 bytes
Variable Size             167772160 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
log_archive_start                    boolean     FALSE
SQL> ARCHIVE LOG LIST;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /tmp/recover
Oldest online log sequence     26
Current log sequence           28

ALERT中无类此记录出现

在日常的性能监控中，我们经常需要需要通过一段时间内数据库的逻辑读/物理读来大致反映数据库的性能情况，这里列出通过awr和statspack来获取相关数据（查询最近30天数据，除掉第一条和最后一条数据）

awr逻辑读

WITH A AS
 (SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
    FROM DBA_HIST_SYSSTAT B
   WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
          AND b.INSTANCE_NUMBER = &INST_NUM
     AND B.STAT_NAME IN ('session logical reads')
   GROUP BY B.SNAP_ID
   ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
       LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
       VALUE "END_VALUE(G)",
       TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')"END_TIME",
       VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
  FROM A,(SELECT END_INTERVAL_TIME,SNAP_ID
    FROM DBA_HIST_SNAPSHOT B
   WHERE  B.DBID = (SELECT dbid FROM v$database)
     AND B.INSTANCE_NUMBER = &INST_NUM) B
     WHERE A.SNAP_ID=B.snap_id
     AND END_INTERVAL_TIME>=SYSDATE-30;

awr物理读

WITH A AS
 (SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
    FROM DBA_HIST_SYSSTAT B
   WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
          AND b.INSTANCE_NUMBER = &INST_NUM
     AND B.STAT_NAME IN ('physical reads')
   GROUP BY B.SNAP_ID
   ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
       LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
       VALUE "END_VALUE(G)",
       TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')"END_TIME",
       VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
  FROM A,(SELECT END_INTERVAL_TIME,SNAP_ID
    FROM DBA_HIST_SNAPSHOT B
   WHERE  B.DBID = (SELECT dbid FROM v$database)
     AND B.INSTANCE_NUMBER = &INST_NUM) B
     WHERE A.SNAP_ID=B.snap_id
     AND END_INTERVAL_TIME>=SYSDATE-30;

statspack逻辑读

WITH A AS
 (SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
    FROM STATS$SYSSTAT B
   WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
     AND B.INSTANCE_NUMBER = &INST_NUM
     AND B.NAME IN ('session logical reads')
   GROUP BY B.SNAP_ID
   ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
       LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
       VALUE "END_VALUE(G)",
       TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS') "END_TIME",
       VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
  FROM A,
       (SELECT SNAP_TIME, SNAP_ID
          FROM STATS$SNAPSHOT B
         WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = &INST_NUM) B
 WHERE A.SNAP_ID = B.SNAP_ID
  AND snap_time>=SYSDATE-30;

statspack物理读

WITH A AS
 (SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
    FROM STATS$SYSSTAT B
   WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
     AND B.INSTANCE_NUMBER = &INST_NUM
     AND B.NAME IN ('physical reads')
   GROUP BY B.SNAP_ID
   ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
       LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
       VALUE "END_VALUE(G)",
       TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS') "END_TIME",
       VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
  FROM A,
       (SELECT SNAP_TIME, SNAP_ID
          FROM STATS$SNAPSHOT B
         WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = &INST_NUM) B
 WHERE A.SNAP_ID = B.SNAP_ID
 AND snap_time>=SYSDATE-30;